If anyone needs it, I could use it. rules as array and using rule():
const { validate, rule } = use('Validator')
doc: [rule("regex", /([0-9]{2}[\.]?[0-9]{3}[\.]?[0-9]{3}[\/]?[0-9]{4}[-]?[0-9]{2})|([0-9]{3}[\.]?[0-9]{3}[\.]?[0-9]{3}[-]?[0-9]{2})/g),
rule("required"),
rule("min", 11),
rule("max", 11),
rule("string")]
Give it a try with this order.
Mockito.when(serviceMock.methodname(params)).thenReturn(responseType);
String json = new Gson().toJson(param);
MockHttpServletRequestBuilder requestBuilder =
MockMvcRequestBuilders.post(BASE_ENDPOINT)
.content(json)
.contentType(MediaType.APPLICATION_JSON)
.accept(MediaType.APPLICATION_JSON)
.characterEncoding(CharEncoding.UTF_8);
ResultActions resultActions = this.mockMvc.perform(requestBuilder);
resultActions
.andExpect(status().isOk())
.andExpect(
MockMvcResultMatchers.jsonPath("$.fields").value(param.value())));
verify(serviceMock, times(1)).method(param);
The way PostgreSQL is implemented, a foreign key references a unique index on the target table, not a list of columns. If you have several unique constraints on a single column, a foreign key constraint referencing that column will reference one of these unique indexes. So if you created the unique constraint first, that's what the foreign key will point to.
The correct solution is to drop the foreign key along with the unique constraint and create it again, so that it points to the primary key. You could create it as NOT VALID and validate it later to avoid holding strong locks for a longer time.
You could also update the pg_constraint entry and modify conindid, but you'd also have to modify the corresponding pg_depend entry to change the dependency. However, modifying catalog tables is not supported, and it is easy to break your database that way, so I cannot recommend this course of action.
I'm following the instructions on https://artifacthub.io/packages/helm/mysql-operator/mysql-operator?modal=install .
helm install my-mysql-operator mysql-operator/mysql-operator --version 2.0.4
But I'm getting the error
Error: INSTALLATION FAILED: execution error at (mysql-operator/templates/service_account_operator.yaml:3:5): Please provide a namespace with
-n/--namespace. The operator cannot be installed in the 'default' namespace
What can I do to resolve this error?
Have a look at the Release notes. You need to run all upgrades one-by-one.
https://www.mongodb.com/docs/manual/release-notes/3.0-upgrade/#upgrade-a-replica-set-to-3.0
https://www.mongodb.com/docs/manual/release-notes/3.2-upgrade/#upgrade-a-replica-set-to-3.2
https://www.mongodb.com/docs/manual/release-notes/3.4-upgrade-replica-set/
...
https://www.mongodb.com/docs/manual/release-notes/5.0-upgrade-replica-set/
Each single upgrade should be possible without service interruption.
The problem was resolved by moving the
creation of the table wp_woocommerce_downloadable_product_permissions before the creation of the table wp_wc_download_log
I have a table defined as:
CREATE TABLE addresses (
"address" varchar(42),
"other_info1" text,
"other_info2" bigint
);
and I have some manual corrections to that table defined as:
CREATE TABLE address_corrections (
"address" varchar(42),
"other_info1" text,
"other_info2" bigint
);
I have a simple union + dedup query that I am persisting as a materialized view corrected_addresses with the same schema as the two tables above. The size of the materialized view is about the same size as the original table addresses, which is about 400k rows. I have an index on the materialized view which matches the same exact index on the original addresses table:
CREATE INDEX corrected_addresses_address_idx ON corrected_addresses(address);
Now when querying some other tables I want to join on this table to get access to other_info1 and other_info2. Simple enough. However, this join appears to not use the index on address. The query looks like this:
EXPLAIN (analyze, buffers, format text)
SELECT transfer.from_name, transfer.to_name, transfer.value,
transfer.address, address.other_info1, address.other_info2
FROM transfers transfer
LEFT JOIN corrected_addresses address ON transfer.address=address.address
WHERE transfer.from_name='some name here';
with either addresses or corrected_addresses as the join table.
Here is the result for the original address table:
Gather (cost=1205.50..43489.92 rows=6241 width=173) (actual time=1.422..12.292 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=12
-> Nested Loop Left Join (cost=205.50..41865.82 rows=2600 width=173) (actual time=0.184..0.198 rows=1 loops=3)
Buffers: shared hit=12
-> Parallel Bitmap Heap Scan on transfers transfer (cost=205.07..25042.94 rows=2600 width=204) (actual time=0.176..0.180 rows=1 loops=3)
Recheck Cond: ((from_name)::text = 'some name here'::text)
Heap Blocks: exact=2
Buffers: shared hit=8
-> Bitmap Index Scan on transfers_from_name_timestamp_idx (cost=0.00..203.51 rows=6241 width=0) (actual time=0.400..0.401 rows=2 loops=1)
Index Cond: ((from_name)::text = 'some name here'::text)
Buffers: shared hit=6
-> Memoize (cost=0.43..7.99 rows=1 width=55) (actual time=0.017..0.018 rows=0 loops=2)
Cache Key: transfer.address
Cache Mode: logical
Hits: 0 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=4
-> Index Scan using addresses_idx on addresses a (cost=0.42..7.98 rows=1 width=55) (actual time=0.013..0.013 rows=0 loops=2)
Index Cond: ((address)::text = (transfer.address)::text)
Buffers: shared hit=4
Planning:
Buffers: shared hit=334 dirtied=1
Planning Time: 5.692 ms
Execution Time: 12.601 ms
And for the materialized view:
Gather (cost=10501.47..35970.26 rows=6241 width=173) (actual time=100.881..111.703 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5327
-> Parallel Hash Left Join (cost=9501.47..34346.16 rows=2600 width=173) (actual time=94.729..95.259 rows=1 loops=3)
Hash Cond: ((transfer.address)::text = (a.address)::text)
Buffers: shared hit=5327
-> Parallel Bitmap Heap Scan on transfers transfer (cost=205.07..25042.94 rows=2600 width=204) (actual time=0.162..0.164 rows=1 loops=3)
Recheck Cond: ((from_name)::text = 'some name here'::text)
Heap Blocks: exact=1
Buffers: shared hit=8
-> Bitmap Index Scan on transfers_from_name_timestamp_idx (cost=0.00..203.51 rows=6241 width=0) (actual time=0.070..0.071 rows=2 loops=1)
Index Cond: ((from_name)::text = 'some name here'::text)
Buffers: shared hit=6
-> Parallel Hash (cost=7051.18..7051.18 rows=179618 width=55) (actual time=92.901..92.901 rows=143694 loops=3)
Buckets: 524288 Batches: 1 Memory Usage: 41856kB
Buffers: shared hit=5255
-> Parallel Seq Scan on corrected_addresses a (cost=0.00..7051.18 rows=179618 width=55) (actual time=0.006..27.550 rows=143694 loops=3)
Buffers: shared hit=5255
Planning:
Buffers: shared hit=37
Planning Time: 0.482 ms
Execution Time: 111.758 ms
Both of those execution times are small, I believe that's partially because of caching. The difference on a fresh run is ~100ms vs. a few seconds. Sorry I couldn't get an example here.
Does anyone know why Postgres is not using the index for the materialized view?
i am using SQL Server, considering the following table:
Content Table
| title | topic | id_user |
|---|---|---|
| Title 1 | Topic1 | 1 |
| Title 2 | Topic1 | 1 |
| Title 3 | Topic2 | 2 |
| Title 4 | Topic1 | 3 |
| Title 5 | Topic1 | 1 |
| Title 6 | Topic1 | 3 |
| Title 7 | Topic2 | 2 |
When I run I get the following:
SELECT topic, id_user, COUNT(*) AS total
FROM Content
GROUP BY topic, id_user
ORDER BY total DESC
Result
| topic | id_user | total |
|---|---|---|
| Topic1 | 1 | 3 |
| Topic1 | 3 | 2 |
| Topic2 | 2 | 2 |
In this case there are two users (there could be more) with Topic1, I only want to show the one with the highest total count value of their respective Topic
For example get the following result:
Expected Result
| topic | id_user | total |
|---|---|---|
| Topic1 | 1 | 3 |
| Topic2 | 2 | 2 |
Any clue how this could be done?
I'm using MariaDB.
I'm designing a simple application that can send notifications to subscribers for when certain websites they've subscribed to have been updated.
A user can be subscribed to several different sites and multiple users can subscribe to same site.
So both tables are independent in a vacuum. Simple schema for both, just a
My tentative flow for efficiency and scalability is something like the following
Is introducing a junction table even necessary for this? I feel like it'd introduce complexity bc I'd have to join on that everytime and update that everytime a change has been made to either subscribers table.
Or.. should I just denormalize..
Seems like sites is kind of the "common denominator" here (i.e. I'll always check if EVERY site has changed, but I don't necessarily need all subscribers)
So given ^, I was thinking of maybe just adding a Sites.subsriber_ids field..
Or..should I just go for a NoSQL approach?
Any thoughts and recommendations ?
Thanks!
CREATE TABLE provsub (
provider_id int(11) NOT NULL,
subscriber_id int(11) NOT NULL,
created timestamp NOT NULL DEFAULT current_timestamp(),
updated timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (provider_id,subscriber_id),
KEY subscriber_id (subscriber_id),
CONSTRAINT provsub_ibfk_1 FOREIGN KEY (provider_id) REFERENCES providers (id),
CONSTRAINT provsub_ibfk_2 FOREIGN KEY (subscriber_id) REFERENCES subscribers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
I try that code;
select *
from `diary`
where date(`created_at`) = 2022-05-10
order by JSON_LENGTH(like) asc limit 1
Like column in my table;
+----+--------------+-------------------------+
| id | like | created_at |
+----+--------------+-------------------------+
| 1 | ['asas'] | 2022-05-10 07:02:42 |
+----+--------------+-------------------------+
Here is my error; You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'like) asc limit 1' at line 1
If I don't use order by, code work fine. How can I fix it?
I have this table
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+---------
l_orderkey | integer | | |
l_partkey | integer | | |
l_suppkey | integer | | |
l_linenumber | integer | | |
l_quantity | integer | | |
l_extendedprice | numeric(12,2) | | |
l_discount | numeric(12,2) | | |
l_tax | numeric(12,2) | | |
l_returnflag | character(1) | | |
l_linestatus | character(1) | | |
l_shipdate | date | | |
l_commitdate | date | | |
l_receiptdate | date | | |
l_shipinstruct | character(25) | | |
l_shipmode | character(10) | | |
l_comment | character(44) | | |
l_partsuppkey | character(20) | | |
Indexes:
"l_shipdate_c_idx" btree (l_shipdate) CLUSTER
"l_shipmode_h_idx" hash (l_shipdate)
Foreign-key constraints:
"lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)
"lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey) REFERENCES part(p_partkey)
"lineitem_l_partsuppkey_fkey" FOREIGN KEY (l_partsuppkey) REFERENCES partsupp(ps_partsuppkey)
"lineitem_l_suppkey_fkey" FOREIGN KEY (l_suppkey) REFERENCES supplier(s_suppkey)
and this query:
explain analyze select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1 - l_discount)) as sum_disc_price,
sum(l_extendedprice*(1 - l_discount)*(1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdatereturning this query plan:
"Finalize GroupAggregate (cost=2631562.25..2631564.19 rows=6 width=212) (actual time=28624.012..28624.466 rows=4 loops=1)"
" Group Key: l_returnflag, l_linestatus"
" -> Gather Merge (cost=2631562.25..2631563.65 rows=12 width=212) (actual time=28623.998..28624.442 rows=12 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Sort (cost=2630562.23..2630562.24 rows=6 width=212) (actual time=28620.633..28620.633 rows=4 loops=3)"
" Sort Key: l_returnflag, l_linestatus"
" Sort Method: quicksort Memory: 27kB"
" Worker 0: Sort Method: quicksort Memory: 27kB"
" Worker 1: Sort Method: quicksort Memory: 27kB"
" -> Partial HashAggregate (cost=2630562.03..2630562.15 rows=6 width=212) (actual time=28620.607..28620.611 rows=4 loops=3)"
" Group Key: l_returnflag, l_linestatus"
" Batches: 1 Memory Usage: 24kB"
" Worker 0: Batches: 1 Memory Usage: 24kB"
" Worker 1: Batches: 1 Memory Usage: 24kB"
" -> Parallel Seq Scan on lineitem (cost=0.00..1707452.35 rows=24616258 width=24) (actual time=0.549..19028.353 rows=19701655 loops=3)"
" Filter: (l_shipdate lineitem table instead of using l_shipdate_c_idx? Should I drop it?Postgres version: PostgreSQL 14.2 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
I have code to query partition data. But when the database creates an index, my query does not work because of the LCK_M_SCH_S lock, although I use the NOLOCK hint for all tables. Is there any way around this lock or get this data without lock?
SELECT DB_NAME() AS DatabaseName
, OBJECT_SCHEMA_NAME(p.OBJECT_ID) as TableschemaName
, OBJECT_NAME(p.OBJECT_ID) AS TableName
, p.index_id AS 'IndexId'
, CASE WHEN p.index_id = 0 THEN 'HEAP'
ELSE i.name
END AS 'IndexName'
, p.partition_number AS PartitionNumber
, prv_left.value AS LowerBoundary
, prv_right.value AS UpperBoundary
, ps.name as PartitionScheme
, pf.name as PartitionFunction
, c.name AS [Partitioning Column]
, TYPE_NAME(c.user_type_id) AS [Column Type]
, CASE WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END AS 'FileGroupName'
, CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS UsedPagesMB
, CAST(p.in_row_data_page_count * 0.0078125 AS NUMERIC(18,2)) AS DataPagesMB
, CAST(p.reserved_page_count * 0.0078125 AS NUMERIC(18,2)) AS ReservedPagesMB
, CASE WHEN p.index_id IN (0,1) THEN p.row_count
ELSE 0
END AS RowsQuantity
,CASE WHEN p.index_id IN (0,1) THEN 'data'
ELSE 'index'
END AS Type
FROM sys.dm_db_partition_stats p WITH (NOLOCK)
JOIN sys.indexes i WITH (NOLOCK) ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.data_spaces ds WITH (NOLOCK) ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.partition_functions pf WITH (NOLOCK) ON ps.function_id = pf.function_id
LEFT JOIN sys.destination_data_spaces dds WITH (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values prv_right WITH (NOLOCK) ON prv_right.function_id = ps.function_id
AND prv_right.boundary_id = p.partition_number
LEFT JOIN sys.partition_range_values prv_left WITH (NOLOCK) ON prv_left.function_id = ps.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.index_columns ic WITH (NOLOCK) ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
AND ic.partition_ordinal >= 1
LEFT JOIN sys.columns c WITH (NOLOCK) ON p.OBJECT_ID = c.[object_id]
AND ic.column_id = c.column_id
WHERE OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
AND p.index_id IN (0,1)
AND ps.name IS NOT NULL