Re: Experiencing error during restore - found unexpected block ID (0)
On 12/16/19 9:46 AM, Sar wrote: Hi, I have database on Windows Server, running postgresql 10 and I've run pg_dump with options: -Fc --verbose -d db_name --file "E:\backup.sql" -T some_table. Now when I run pg_restore, also on windows server with postgresql 12, or on the same original server with postgresql 10, using options --data-only --verbose -j 8, or without jobs, but selecting some specific few tables with -t option I get follwing error: Which Postgres version of pg_dump/pg_restore are you using to do above? error: pg_restore: error: found unexpected block ID (0) when reading data -- expected 3142 This error will only happen on very few tables, most run fine. What happens if you SELECT from these tables in the Postgres 10 instance? Can you show the schema definition for one or more of the problem tables? Currently, I'm running without jobs and without tables selected with -t, however I haven't gotten to the specific table that causes the trouble (the restore will take days), however I already fear when it comes to that table the same error will trigger. I guess this could serve as a possible bug report as well, but any advice as to what I can do is appreciated. -- Adrian Klaver adrian.kla...@aklaver.com
Experiencing error during restore - found unexpected block ID (0)
Hi, I have database on Windows Server, running postgresql 10 and I've run pg_ dump with options: -Fc --verbose -d db_name --file "E:\backup.sql" -T some_ table. Now when I run pg_restore, also on windows server with postgresql 12, or on the same original server with postgresql 10, using options --data-only -- verbose -j 8, or without jobs, but selecting some specific few tables with -t option I get follwing error: error: pg_restore: error: found unexpected block ID (0) when reading data -- expected 3142 This error will only happen on very few tables, most run fine. Currently, I'm running without jobs and without tables selected with -t, however I haven't gotten to the specific table that causes the trouble (the restore will take days), however I already fear when it comes to that table the same error will trigger. I guess this could serve as a possible bug report as well, but any advice as to what I can do is appreciated.
Re: Race condition while creating a new partition
It looks like you are creating a partition for each minute of the day (when logs get inserted for a given minute at least). Would it be at all reasonable to have an hourly or daily job which creates the partitions ahead of when they are actually needed? If partitions went unused in the recent past, you could also drop those if appropriate to your workflow. I just wonder about doing the work just-in-time via 2+ connections vs doing it from a single process to avoid the chance of deadlock completely.
Re: Race condition while creating a new partition
Hi Andrei General speaking any DDL (Create, Alter Drop .etc) commands issue exclusive locks automatically, so anything this transaction touches starts getting exclusive locks. Assuming this is a multi-threading app these two threads are sending commands all but at the same time. The Exclusive lock has not been committed yet as its waiting for a previous insert to finish, and the Exclusive lock is blocking the insert as its transaction started prior to the Insert. the wait queue is pretty long 84, 80, 82, 83, 87, 88, 85, 86. The time stamps are only 2 ms apart, lots of commands being sent in at nearly the same time... Here are my suggestions Tweak the Python app to make sure all the threads have committed their transactions place a lock on all the other threads, Then issue a DDL command, when the command is committed, then unlock the other threads. That should kill off the race condition deadlock.. Post all the SQL code that is creating this exclusive lock, we may be able re-write the SQL code to reduce the time and/or resources this Exclusive Lock is creating. Pyscopg2 default mode is to require issuing a Commit it does not automatically commit transaction. If running in auto-commit OFF try turning this ON for DDL commands may fix the issue. It may also be that DDL command takes far longer than 1 second to complete...
Re: Race condition while creating a new partition
Hi, Justin
I’ve managed to reproduce this deadlock (different threads) and it looks it
happens while Postgres tries to insert data into unique index for pg_type table
(it creates a new row-type for every new table and a new partition is a new
table). 16453 is old of the parent's table for the partition. But I still can’t
understand why do we have a deadlock here.
2019-12-16 11:41:17.895 UTC [79] LOG: process 79 detected deadlock while
waiting for ShareLock on transaction 599 after 1000.210 ms
2019-12-16 11:41:17.895 UTC [79] DETAIL: Process holding the lock: 81. Wait
queue: 84, 80, 82, 83, 87, 88, 85, 86.
2019-12-16 11:41:17.895 UTC [79] CONTEXT: while inserting index tuple (10,35)
in relation "pg_type_typname_nsp_index"
2019-12-16 11:41:17.895 UTC [79] STATEMENT: INSERT INTO test (val) VAlUES
('thread 6')
2019-12-16 11:41:17.897 UTC [79] ERROR: deadlock detected
2019-12-16 11:41:17.897 UTC [79] DETAIL: Process 79 waits for ShareLock on
transaction 599; blocked by process 81.
Process 81 waits for AccessExclusiveLock on relation 16453 of database
16384; blocked by process 79.
Process 79: INSERT INTO test (val) VAlUES ('thread 6')
Process 81: INSERT INTO test (val) VAlUES ('thread 4')
2019-12-16 11:41:17.897 UTC [79] CONTEXT: while inserting index tuple (10,35)
in relation "pg_type_typname_nsp_index"
2019-12-16 11:41:17.897 UTC [79] STATEMENT: INSERT INTO test (val) VAlUES
('thread 6')
2019-12-16 11:41:17.982 UTC [81] LOG: process 81 still waiting for
AccessExclusiveLock on relation 16453 of database 16384 after 1036.385 ms
2019-12-16 11:41:17.982 UTC [81] DETAIL: Process holding the lock: 86. Wait
queue: 81.
2019-12-16 11:41:17.982 UTC [81] STATEMENT: INSERT INTO test (val) VAlUES
('thread 4')
2019-12-16 11:41:17.991 UTC [81] LOG: process 81 acquired AccessExclusiveLock
on relation 16453 of database 16384 after 1044.976 ms
2019-12-16 11:41:17.991 UTC [81] STATEMENT: INSERT INTO test (val) VAlUES
('thread 4')
> On 16. Dec 2019, at 16:03, Justin wrote:
>
> Hi Andrei,
>
> My gut reactions is Yes this is a deadlock caused by a race condition, the
> error from psycopg2 tells us that. Question becomes what is causing these
> two process to collide, are both processes 33 and 37 python code, As both
> are trying to access the same resource 16453 i would assume both sending the
> same command Create Table Partition. Are these two connections from
> different computers or the same computer using multi threading??
>
> What does Postgresql Log show what is going on??
>
> On Mon, Dec 16, 2019 at 5:32 AM Andrei Zhidenkov > wrote:
> I think that I’ve got a deadlock (which is handled by `exception when others`
> statements). But the problem is it occurs too fast. Is it possible to get a
> deadlock faster than deadlock_timeout? It’s set to 1s (default value) but it
> looks like I get it immidiately. Error message I’m getting after removing the
> second exception handling is the following:
>
> psycopg2.errors.DeadlockDetected: deadlock detected
> DETAIL: Process 33 waits for AccessExclusiveLock on relation 16453 of
> database 16384; blocked by process 37.
> Process 37 waits for AccessExclusiveLock on relation 16453 of database 16384;
> blocked by process 33.
> HINT: See server log for query details.
> CONTEXT: SQL statement "
> CREATE TABLE IF NOT EXISTS
> prov_level_1.log_*__2019_12_16_10_25_46 PARTITION OF prov_level_1.log_*
> FOR VALUES FROM ('2019-12-16 10:25:46+00') TO ('2019-12-16
> 10:25:47+00');
> "
>
> > On 15. Nov 2019, at 11:49, Andrei Zhidenkov > > wrote:
> >
> > We use this code in order to automatically create new partitions for a
> > partitioned table (Postgres 10.6):
> >
> > begin
> > insert into ;
> > exception when undefined_table then
> > begin
> >
> >-- A concurrent txn has created the new partition
> >exception when others then end;
> >-- Insert data into the new partition
> >insert into ;
> > end;
> > end;
> >
> > As far as I understand we should always have a new partition created either
> > in current or in concurrent transaction but today we faced the problem when
> > we failed to insert data due to a partition nonexistence for a small period
> > of time. Have I missed something?
> >
> > Thank you.
> >
> > —
> >
> > With best regards, Andrei Zhidenkov.
>
>
>
Re: Race condition while creating a new partition
Hi Andrei,
My gut reactions is Yes this is a deadlock caused by a race condition, the
error from psycopg2 tells us that. Question becomes what is causing these
two process to collide, are both processes 33 and 37 python code, As both
are trying to access the same resource 16453 i would assume both sending
the same command Create Table Partition. Are these two connections from
different computers or the same computer using multi threading??
What does Postgresql Log show what is going on??
On Mon, Dec 16, 2019 at 5:32 AM Andrei Zhidenkov
wrote:
> I think that I’ve got a deadlock (which is handled by `exception when
> others` statements). But the problem is it occurs too fast. Is it possible
> to get a deadlock faster than deadlock_timeout? It’s set to 1s (default
> value) but it looks like I get it immidiately. Error message I’m getting
> after removing the second exception handling is the following:
>
> psycopg2.errors.DeadlockDetected: deadlock detected
> DETAIL: Process 33 waits for AccessExclusiveLock on relation 16453 of
> database 16384; blocked by process 37.
> Process 37 waits for AccessExclusiveLock on relation 16453 of database
> 16384; blocked by process 33.
> HINT: See server log for query details.
> CONTEXT: SQL statement "
> CREATE TABLE IF NOT EXISTS
> prov_level_1.log_*__2019_12_16_10_25_46 PARTITION OF prov_level_1.log_*
> FOR VALUES FROM ('2019-12-16 10:25:46+00') TO ('2019-12-16
> 10:25:47+00');
> "
>
> > On 15. Nov 2019, at 11:49, Andrei Zhidenkov
> wrote:
> >
> > We use this code in order to automatically create new partitions for a
> partitioned table (Postgres 10.6):
> >
> > begin
> > insert into ;
> > exception when undefined_table then
> > begin
> >
> >-- A concurrent txn has created the new partition
> >exception when others then end;
> >-- Insert data into the new partition
> >insert into ;
> > end;
> > end;
> >
> > As far as I understand we should always have a new partition created
> either in current or in concurrent transaction but today we faced the
> problem when we failed to insert data due to a partition nonexistence for a
> small period of time. Have I missed something?
> >
> > Thank you.
> >
> > —
> >
> > With best regards, Andrei Zhidenkov.
>
>
>
>
Re: Access privileges
## Daulat Ram (daulat@exponential.com): > Can you please describe the > " =Tc/postgres + postgres=CTc/postgres +confluence=CTc/postgres". It's all here: https://www.postgresql.org/docs/current/ddl-priv.html Regards, Christoph -- Spare Space
Re: Event Triggers and Dropping Objects
Le lundi 16 décembre 2019 à 11:13:10 UTC+1, Luca Ferrari a écrit : On Mon, Oct 7, 2019 at 10:12 PM Bruce Momjian wrote: > Do the Postgres docs need improvement here? I don't know, but I would probably add a flag column in the firing matrix to indicate when the related function will return a null tuple. Luca Hi folks, i think the docs are confusing, in chapter 39.1 it tell us that the following event trigger ddl_command_start and ddl_command_end are trapping the DROP but in the real they returns nothing usable. The matrix in chapter 39.2 also tells us that DROP is trapped by these two. I'm also wondering using event trigger sql_drop why 3 lines are returned : zof [20410]=# CREATE OR REPLACE FUNCTION ddl_log_sql_drop()zof-# RETURNS event_triggerzof-# LANGUAGE plpgsqlzof-# SECURITY DEFINERzof-# AS $function$zof$# DECLAREzof$# obj record;zof$# BEGINzof$# FOR obj INzof$# SELECT *zof$# FROM pg_catalog.pg_event_trigger_dropped_objects()zof$# LOOPzof$# RAISE NOTICE 'DROP objet : % % % %',zof$# obj.object_type,zof$# obj.schema_name,zof$# obj.object_name,zof$# obj.object_identity;zof$# END LOOP;zof$# END;zof$# $function$; CREATE EVENT TRIGGER ddl_log_sql_drop ON sql_dropNOTICE: CREATE FUNCTION objet : function zof.zof.ddl_log_sql_drop() EXECUTE PROCEDURE ddl_log_sql_drop(); CREATE FUNCTION zof [20410]=# CREATE EVENT TRIGGER ddl_log_sql_drop ON sql_dropzof-# EXECUTE PROCEDURE ddl_log_sql_drop();CREATE EVENT TRIGGER zof [20410]=# drop table za; NOTICE: DROP objet : table zof za zof.zaNOTICE: DROP objet : type zof za zof.zaNOTICE: DROP objet : type zof _za zof.za[]DROP TABLE ThxOlf
Re: Race condition while creating a new partition
I think that I’ve got a deadlock (which is handled by `exception when others`
statements). But the problem is it occurs too fast. Is it possible to get a
deadlock faster than deadlock_timeout? It’s set to 1s (default value) but it
looks like I get it immidiately. Error message I’m getting after removing the
second exception handling is the following:
psycopg2.errors.DeadlockDetected: deadlock detected
DETAIL: Process 33 waits for AccessExclusiveLock on relation 16453 of database
16384; blocked by process 37.
Process 37 waits for AccessExclusiveLock on relation 16453 of database 16384;
blocked by process 33.
HINT: See server log for query details.
CONTEXT: SQL statement "
CREATE TABLE IF NOT EXISTS prov_level_1.log_*__2019_12_16_10_25_46
PARTITION OF prov_level_1.log_*
FOR VALUES FROM ('2019-12-16 10:25:46+00') TO ('2019-12-16
10:25:47+00');
"
> On 15. Nov 2019, at 11:49, Andrei Zhidenkov wrote:
>
> We use this code in order to automatically create new partitions for a
> partitioned table (Postgres 10.6):
>
> begin
> insert into ;
> exception when undefined_table then
> begin
>
>-- A concurrent txn has created the new partition
>exception when others then end;
>-- Insert data into the new partition
>insert into ;
> end;
> end;
>
> As far as I understand we should always have a new partition created either
> in current or in concurrent transaction but today we faced the problem when
> we failed to insert data due to a partition nonexistence for a small period
> of time. Have I missed something?
>
> Thank you.
>
> —
>
> With best regards, Andrei Zhidenkov.
Access privileges
Hello, Can you please describe the " =Tc/postgres + postgres=CTc/postgres +confluence=CTc/postgres". I want to give the same permissions to the different user on a new database. List of databases Name| Owner | Encoding | Collate | Ctype |Access privileges +--+--+-+-+- confluence | postgres | UTF8 | C | C | =Tc/postgres + | postgres=CTc/postgres + | confluence=CTc/postgres Thanks,
Re: Is there any tool that provides Physical Backup plus PITR for a single database ( Not the whole PG instance ) ?
Hello! pg_probackup can do that: https://postgrespro.github.io/pg_probackup/#pbk-partial-restore On 12/15/19 5:32 PM, Abraham, Danny wrote: I assume that this magic does not exist. Am I right ? -- Grigory Smolkin Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
