Re: Rows violating Foreign key constraint exists
Hi, > Have you verified that the FK is not in the parent table and is just not > some index error/corruption? Yes. > > > > Also, is there any way to make sure the FK checking trigger can never > > be disabled (so that such a case will never arise)? > > Not sure that can happen as it is baked into existing code. For instance: > > https://www.postgresql.org/docs/11/app-pgdump.html > > " > --disable-triggers > > This option is relevant only when creating a data-only dump. It > instructs pg_dump to include commands to temporarily disable triggers on > the target tables while the data is reloaded. Use this if you have > referential integrity checks or other triggers on the tables that you do > not want to invoke during data reload. Found this thread which discusses the same topic as here. https://www.postgresql.org/message-id/20190715160926.GA17140%40alvherre.pgsql PG already allows a new FK to be created with ADD CONSTRAINT ... NOT VALID clause which can be validated later using ALTER TABLE ... VALIDATE CONSTRAINT. I guess what we are looking for here is the same but for existing FKs. i.e Something like `ALTER TABLE distributors ALTER CONSTRAINT distfk NOT VALID;` `ALTER TABLE distributors VALIDATE CONSTRAINT distfk;` Regards, Nanda
Re: Rows violating Foreign key constraint exists
Hi, > PG already allows a new FK to be created with ADD CONSTRAINT ... NOT > VALID clause which can be validated later using ALTER TABLE ... > VALIDATE CONSTRAINT. > I guess what we are looking for here is the same but for existing FKs. > > i.e Something like > > `ALTER TABLE distributors ALTER CONSTRAINT distfk NOT VALID;` > `ALTER TABLE distributors VALIDATE CONSTRAINT distfk;` > I was wrong about this. ADD CONSTRAINT ... NOT VALID just postpones integrity existing on existing data. There is no equivalent for that when altering an existing FK. Maybe, DISABLE ALL TRIGGERS can mark the FKs as NOT VALID. ALTER TABLE ... VALIDATE CONSTRAINT would now report inconsistencies if any. Also, VALIDATE CONSTRAINT would have to verify that the trigger implementing FK is enabled. Hope/unsure if this would not break backwards compatibility. Regards, Nanda
Re: Rows violating Foreign key constraint exists
Nandakumar M writes: >> It could be that somebody disabled the triggers, but that would have to >> be a superuser. And I hope that people randomly disabling system triggers >> on tables don't have superuser access to your database. > It is unlikely that this happened. So I am assuming corruption. > But I am able to query both the referred and referring table > successfully without any 'missing chunk' or similar errors that > usually indicate corruption. > Is it possible that corruption might cause data loss like this without > any errors? The most likely "corruption" explanation is something wrong with the indexes on the referenced and/or referencing column, causing rows to not be found when referential actions should have found them. Random querying of the tables wouldn't necessarily expose that --- you'd need to be sure that your queries use the questionable indexes, and maybe even search for some of the specific rows that seem mis-indexed. regards, tom lane
Counting booleans in GROUP BY sections
Good evening,
I am trying to count the booleans per each GROUP BY section by the
following stored function:
CREATE OR REPLACE FUNCTION words_list_puzzles(
in_start interval,
in_end interval
) RETURNS TABLE (
out_label text,
out_count bigint,
out_puzzle boolean,
out_midbigint,
out_secret text,
out_gidinteger,
out_score integer
) AS
$func$
SELECT
TO_CHAR(played, 'Mon ') AS label,
COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
puzzle,
mid,
MD5(mid || ‘my little secret’) AS secret,
gid,
score
FROM words_moves
WHERE action = 'play'
AND LENGTH(hand) = 7
AND (LENGTH(letters) = 7 OR score > 90)
AND played > CURRENT_TIMESTAMP - in_start
AND played < CURRENT_TIMESTAMP - in_end
GROUP BY label, puzzle, mid, secret, gid, score
ORDER BY played DESC
$func$ LANGUAGE sql STABLE;
But when I run it, I only get 0 or 1 in the out_count column:
words_ru=> select * from words_list_puzzles(interval '2 year', interval '1
year');
out_label | out_count | out_puzzle | out_mid |out_secret
| out_gid | out_score
---+---++-+--+-+---
Nov 2018 | 0 | f | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe | 46007 |28
Nov 2018 | 0 | f | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 | 45922 |26
Nov 2018 | 0 | f | 1322050 |
b67b091d383678de392bf7370c735cab | 45877 |34
Nov 2018 | 0 | f | 1320017 |
35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
Nov 2018 | 0 | f | 1319160 |
83df42f7ad398bbb060fc02ddfdc62c0 | 45031 |95
.
May 2018 | 0 | f | 264251 |
2fff1154962966b16a2996387e30ae7f | 10946 |99
May 2018 | 1 | t | 257620 |
645613db6ea40695dc967d8090ab3246 | 12713 |93
May 2018 | 0 | f | 245792 |
bb75bfd9cb443ff541b199d893c68117 | 12359 |24
May 2018 | 1 | t | 243265 |
d899a5d642ccd96d931194f48ef56d53 | 11950 | 123
May 2018 | 0 | f | 231953 |
ad53b5b2c0d4cced3d50e8b44ad53e55 | 11910 |32
- while I was hoping to get 2 for the "May 2018" section.
What am I doing wrong please, why don't the values add up? Below is the
table desc:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Thank you
Alex
MS Access Frontend
I am trying to setup MS Access as a frontend so that it would be easier on my wife and children to interact with PostgreSQL. I looked online for some tutorials but the ones I found are out-date or only pick up after Access is connected to PostgreSQL. I was wondering if someone knew of some updated material that they could point me to or maybe walk me through it. I have used Access quite a bit years ago and things have changed since then. I know I must install the ODBC drivers, which I have already done. I have already setup the DSN and I clicked on test and it says everything is fine. I know that my next step has something to do with Linked Tables in Access, but I am not sure how to set it up. I guess that is where I start to need help. The client computers using the frontend will be running Windows 10 and Office 365, both are updated to the latest versions. Thank you, Jason L. Amerson
Re: MS Access Frontend
On 11/29/19 9:23 AM, Jason L. Amerson wrote: I am trying to setup MS Access as a frontend so that it would be easier on my wife and children to interact with PostgreSQL. I looked online for some tutorials but the ones I found are out-date or only pick up after Access is connected to PostgreSQL. I was wondering if someone knew of some updated material that they could point me to or maybe walk me through it. I have used Access quite a bit years ago and things have changed since then. I know I must install the ODBC drivers, which I have already done. I have already setup the DSN and I clicked on test and it says everything is fine. I know that my next step has something to do with Linked Tables in Access, but I am not sure how to set it up. I guess that is where I start to need help. The client computers using the frontend will be running Windows 10 and Office 365, both are updated to the latest versions. This might help: https://support.office.com/en-us/article/Manage-linked-tables-1d9346d6-953d-4f85-a9ce-4caec2262797 Thank you, Jason L. Amerson -- Adrian Klaver adrian.kla...@aklaver.com
Re: Counting booleans in GROUP BY sections
On 11/29/19 8:38 AM, Alexander Farber wrote:
Good evening,
I am trying to count the booleans per each GROUP BY section by the
following stored function:
CREATE OR REPLACE FUNCTION words_list_puzzles(
in_start interval,
in_end interval
) RETURNS TABLE (
out_label text,
out_count bigint,
out_puzzle boolean,
out_mid bigint,
out_secret text,
out_gid integer,
out_score integer
) AS
$func$
SELECT
TO_CHAR(played, 'Mon ') AS label,
COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
If I am following it is because you have mid in GROUP BY and mid is a
PK. Since mid will always be unique you will have at most on row per group.
puzzle,
mid,
MD5(mid || ‘my little secret’) AS secret,
gid,
score
FROM words_moves
WHERE action = 'play'
AND LENGTH(hand) = 7
AND (LENGTH(letters) = 7 OR score > 90)
AND played > CURRENT_TIMESTAMP - in_start
AND played < CURRENT_TIMESTAMP - in_end
GROUP BY label, puzzle, mid, secret, gid, score
ORDER BY played DESC
$func$ LANGUAGE sql STABLE;
But when I run it, I only get 0 or 1 in the out_count column:
words_ru=> select * from words_list_puzzles(interval '2 year', interval
'1 year');
out_label | out_count | out_puzzle | out_mid | out_secret
| out_gid | out_score
---+---++-+--+-+---
Nov 2018 | 0 | f | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe | 46007 | 28
Nov 2018 | 0 | f | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 | 45922 | 26
Nov 2018 | 0 | f | 1322050 |
b67b091d383678de392bf7370c735cab | 45877 | 34
Nov 2018 | 0 | f | 1320017 |
35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
Nov 2018 | 0 | f | 1319160 |
83df42f7ad398bbb060fc02ddfdc62c0 | 45031 | 95
.
May 2018 | 0 | f | 264251 |
2fff1154962966b16a2996387e30ae7f | 10946 | 99
May 2018 | 1 | t | 257620 |
645613db6ea40695dc967d8090ab3246 | 12713 | 93
May 2018 | 0 | f | 245792 |
bb75bfd9cb443ff541b199d893c68117 | 12359 | 24
May 2018 | 1 | t | 243265 |
d899a5d642ccd96d931194f48ef56d53 | 11950 | 123
May 2018 | 0 | f | 231953 |
ad53b5b2c0d4cced3d50e8b44ad53e55 | 11910 | 32
- while I was hoping to get 2 for the "May 2018" section.
What am I doing wrong please, why don't the values add up? Below is the
table desc:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
letters | text | | |
hand | text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES
words_games(gid) ON DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Thank you
Alex
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Rows violating Foreign key constraint exists
On Fri, Nov 29, 2019 at 7:23 AM Tom Lane wrote: > The most likely "corruption" explanation is something wrong with the > indexes on the referenced and/or referencing column, causing rows to > not be found when referential actions should have found them. Random > querying of the tables wouldn't necessarily expose that --- you'd need > to be sure that your queries use the questionable indexes, and maybe > even search for some of the specific rows that seem mis-indexed. Or try using contrib/amcheck, which is available in Postgres 10. Perhaps try the query here, modified to verify all B-Tree indexes (not just those indexes in the pg_catalog schema): https://www.postgresql.org/docs/10/amcheck.html -- Peter Geoghegan
Re: Counting booleans in GROUP BY sections
Thank you Adrian, but -
On Fri, Nov 29, 2019 at 6:45 PM Adrian Klaver
wrote:
> On 11/29/19 8:38 AM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_list_puzzles(
> > in_start interval,
> > in_end interval
> >
> > ) RETURNS TABLE (
> > out_label text,
> > out_count bigint,
> > out_puzzle boolean,
> > out_midbigint,
> > out_secret text,
> > out_gidinteger,
> > out_score integer
> > ) AS
> > $func$
> >
> > SELECT
> > TO_CHAR(played, 'Mon ') AS label,
> > COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
>
> If I am following it is because you have mid in GROUP BY and mid is a
> PK. Since mid will always be unique you will have at most on row per group.
>
> > puzzle,
> > mid,
> > MD5(mid || ‘my little secret’) AS secret,
> > gid,
> > score
> >
> > FROM words_moves
> > WHERE action = 'play'
> > AND LENGTH(hand) = 7
> > AND (LENGTH(letters) = 7 OR score > 90)
> > AND played > CURRENT_TIMESTAMP - in_start
> > AND played < CURRENT_TIMESTAMP - in_end
> > GROUP BY label, puzzle, mid, secret, gid, score
> > ORDER BY played DESC
> >
> > $func$ LANGUAGE sql STABLE;
> >
> > But when I run it, I only get 0 or 1 in the out_count column:
> >
> > words_ru=> select * from words_list_puzzles(interval '2 year', interval
> > '1 year');
> > out_label | out_count | out_puzzle | out_mid |out_secret
> > | out_gid | out_score
> >
> ---+---++-+--+-+---
> > Nov 2018 | 0 | f | 1326876 |
> > e4928d3c34f50b8e6eabf7bad5b932fe | 46007 |28
> > Nov 2018 | 0 | f | 1324466 |
> > 6228ba509a7124f485feb5c1acbb6b68 | 45922 |26
> > Nov 2018 | 0 | f | 1322050 |
> > b67b091d383678de392bf7370c735cab | 45877 |34
> > Nov 2018 | 0 | f | 1320017 |
> > 35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
> > Nov 2018 | 0 | f | 1319160 |
> > 83df42f7ad398bbb060fc02ddfdc62c0 | 45031 |95
> > .
> > May 2018 | 0 | f | 264251 |
> > 2fff1154962966b16a2996387e30ae7f | 10946 |99
> > May 2018 | 1 | t | 257620 |
> > 645613db6ea40695dc967d8090ab3246 | 12713 |93
> > May 2018 | 0 | f | 245792 |
> > bb75bfd9cb443ff541b199d893c68117 | 12359 |24
> > May 2018 | 1 | t | 243265 |
> > d899a5d642ccd96d931194f48ef56d53 | 11950 | 123
> > May 2018 | 0 | f | 231953 |
> > ad53b5b2c0d4cced3d50e8b44ad53e55 | 11910 |32
> >
> > - while I was hoping to get 2 for the "May 2018" section.
> >
> > What am I doing wrong please, why don't the values add up? Below is the
> > table desc:
> >
> > words_ru=> \d words_moves
> >Table "public.words_moves"
> > Column | Type | Collation | Nullable |
> >Default
> >
> -+--+---+--+--
> > mid | bigint | | not null |
> > nextval('words_moves_mid_seq'::regclass)
> > action | text | | not null |
> > gid | integer | | not null |
> > uid | integer | | not null |
> > played | timestamp with time zone | | not null |
> > tiles | jsonb| | |
> > score | integer | | |
> > letters | text | | |
> > hand| text | | |
> > puzzle | boolean | | not null | false
> > Indexes:
> > "words_moves_pkey" PRIMARY KEY, btree (mid)
> > "words_moves_gid_played_idx" btree (gid, played DESC)
> > "words_moves_uid_action_played_idx" btree (uid, action, played)
> > "words_moves_uid_idx" btree (uid)
> > Check constraints:
> > "words_moves_score_check" CHECK (score >= 0)
> > Foreign-key constraints:
> > "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES
> > words_games(gid) ON DELETE CASCADE
> > "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES
> > words_users(uid) ON DELETE CASCADE
> > Referenced by:
> > TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> > (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
> >
>
>
if I remove GROUP BY mid, then I get the error:
ERROR: 42803: column "words_moves.mid" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 18: mid,
^
LOC
pg_basebackup + incremental base backups
Our stream replication slave server got out of sync so we need to base backup again. In case of big databases, can we do incremental backups with pg_basebackup? Is there any alternative? Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup
Re: How to get column and identifier names in UPPERCASE in postgres?
Hi Amine,
El mié., 20 de noviembre de 2019 10:11, Amine Tengilimoglu <
aminetengilimo...@gmail.com> escribió:
> Hi all;
>
> I want to get the column and other identifier names in UPPERCASE
> form rather than a lowercase one without changing application code like
> qouting the identifiers. Do you know any settings function or extention
> for this purpose?
>
Use double quotes (") - for instance "UPPERCASE"
> thank you.
>
Re: Counting booleans in GROUP BY sections
On Fri, Nov 29, 2019 at 12:48 PM Alexander Farber < alexander.far...@gmail.com> wrote: > > if I remove GROUP BY mid, then I get the error: > > ERROR: 42803: column "words_moves.mid" must appear in the GROUP BY clause > or be used in an aggregate function > LINE 18: mid, > ^ > LOCATION: check_ungrouped_columns_walker, parse_agg.c:1369 > > Yes, you need to decide whether you want to output GROUPS (in which case any detail more specific than your desired group needs to be aggregated) or NOT (in which case you can probably use WINDOW functions to accomplish your goal - count(...) OVER (PARTITION BY )) David J.
Re: Counting booleans in GROUP BY sections
On 11/29/19 11:47 AM, Alexander Farber wrote: Thank you Adrian, but - if I remove GROUP BY mid, then I get the error: ERROR: 42803: column "words_moves.mid" must appear in the GROUP BY clause or be used in an aggregate function LINE 18: mid, ^ LOCATION: check_ungrouped_columns_walker, parse_agg.c:1369 Yes because it is a case of opposing forces. When you remove mid from the GROUP BY you get an single row for each group that has an aggregated output where you can have count of > 1. In that case the database has more then one choice for the mid to display and so it throws the error. To go forward it would help to know what it is you are trying to achieve? Regards Alex -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_basebackup + incremental base backups
On Fri, Nov 29, 2019 at 04:57:11PM -0300, Christopher Pereira wrote: > Our stream replication slave server got out of sync so we need to base > backup again. > > In case of big databases, can we do incremental backups with pg_basebackup? I know of two ways to define such backups, one being actually incremental and the other differential: - In the shape of a range of WAL segments, which is incremental. A full base backup is taken, and then by backing up periodically a range of WAL segments to make sure that you are able to recover up to the point you are looking for. Postgres core can help to do that by itself. - Using a combination of full backups and differential backups (differential), the latter containing only (well, mostly) relation pages which have changed since the last full backup or differential backup. When restoring, you then need to merge the last full backup and one or more differential backups, followed by a replay of WAL segments up to the point you are willing to recover to. In this case some external tools offer solutions to that problem: pgBackRest and pg_rman are two I know of. Other backup solutions on top of the two cited above, like barman, all have implementations to handle the first type of incremental backup. > Is there any alternative? > > Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup Yeah, nothing has been done in uptream though in this area. That's more about differential backups. -- Michael signature.asc Description: PGP signature
