Re: Lock acquisition for partition table when setting generic plan
"yotsunaga.na...@fujitsu.com" writes: > I did the following. > Even though I accessed one partition table(test2 table), I also acquired > locks on other partition tables(test1 table). > I expected to acquire locks on the parent table(test table) and the partition > table to access(test2 table). > Why does this happen? You specified a generic plan: > postgres=# set plan_cache_mode = force_generic_plan ; so you are not going to get any plan-time optimization based on knowing the id parameter. Therefore the plan must include sub-plan nodes for every child table, so executing it requires locking all those tables to make sure their schemas haven't changed. regards, tom lane
Upgrading from 9.6 to 12
Hello, We're currently in the beginning phases of a project to upgrade our Postgres cluster from 9.6 to a newer version (most likely v12). This is actually going to be a two part move since we're going to move from a RHEL-backed cluster to an Ubuntu server-backed one. I was wondering if there are any potential pitfalls we should look out for, particularly considering we're jumping up 4 major version and tossing in an OS change for good measure. Best Regards, Kasun Kulathunga
Problem with SqlState=23505 when inserting rows
Hi, I'm new to pg and want to port an application and database from ms sql to pg v12. Together with my application a database containing initial standard data which is needed by the application is also installed. This is done via execution of SQL scripts. Every table of the db has an id column and id column values are also contained in the SQL script which is necessary for consistence. In MS SQL id columns were defined as id INT IDENTITY PRIMARY KEY In PG id columns are defined as id SERIAL PRIMARY KEY Importing the SQL script for initial standard data with PG Admin 4 works without any problem. But afterwards I get error messages with SqlState=23505 when inserting new rows into that databases by my application. My application does not provide id values when doing INSERT INTO statements so that new id values are automatically provided by the database. This worked in MS SQL DB without any problems. But in PG there seem to be conflicts between the row id values of the rows that were initially imported and the row id values which are automatically provided by the database if values for id column are missing in INSERT INTO When automatically providing id values PG seems to ignore the already existing id values. I've tried to using the newer definition: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY but that does not solve the problem. Is there a way to define id columns to that when the database provides values, it recognizes already existing values avoiding conflicts. Any hint is highly appreciated. Regards Werner
Re: Upgrading from 9.6 to 12
On 1/15/20 8:19 AM, Kasun Kulathunga wrote: Hello, We're currently in the beginning phases of a project to upgrade our Postgres cluster from 9.6 to a newer version (most likely v12). This is actually going to be a two part move since we're going to move from a RHEL-backed cluster to an Ubuntu server-backed one. I was wondering if there are any potential pitfalls we should look out for, particularly considering we're jumping up 4 major version and tossing in an OS change for good measure. Start by: 1) Reading Release Notes for the first release of each version: https://www.postgresql.org/docs/10/release-10.html https://www.postgresql.org/docs/11/release-11.html https://www.postgresql.org/docs/12/release-12.html This will tell you what changed, most importantly the incompatibilities. 2) Read up on the Ubuntu packages (assuming you are using the PGDG repos): https://www.postgresql.org/download/linux/ubuntu/ https://wiki.postgresql.org/wiki/Apt Best Regards, Kasun Kulathunga -- Adrian Klaver adrian.kla...@aklaver.com
Re: Problem with SqlState=23505 when inserting rows
On 1/15/20 8:40 AM, Werner Kuhnle wrote:
Hi,
I'm new to pg and want to port an application and database from ms sql to pg
v12.
Together with my application a database containing initial standard data
which is needed by the application is also installed.
This is done via execution of SQL scripts.
Every table of the db has an id column and id column values are also contained
in the SQL script
which is necessary for consistence.
In MS SQL id columns were defined as
id INT IDENTITY PRIMARY KEY
In PG id columns are defined as
id SERIAL PRIMARY KEY
Importing the SQL script for initial standard data with PG Admin 4 works
without any problem.
But afterwards I get error messages with SqlState=23505 when inserting new rows
into that databases by my application.
My application does not provide id values when doing INSERT INTO statements so
that new id values are automatically provided by the database.
This worked in MS SQL DB without any problems.
But in PG there seem to be conflicts between the row id values of the rows that
were
initially imported and the row id values which are automatically provided by
the database
if values for id column are missing in INSERT INTO
When automatically providing id values PG seems to ignore the already existing
id values.
I've tried to using the newer definition:
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
but that does not solve the problem.
Is there a way to define id columns to that when the database provides values,
it recognizes already existing values avoiding conflicts.
Any hint is highly appreciated.
My guess is that the SERIAL was defined for the column, then the data
was added with id values, but the counter for the sequence behind the
SERIAL was not updated to a value greater the the last id added. To verify:
1) In psql do \d some_table and look for the sequence name in the
default column e.g:
production=# \d order_header
Table
"public.order_header"
Column| Type | Collation | Nullable |
Default
--++---+--+--
order_no | integer| | not null |
nextval('order_header_order_no_seq'::text::regclass)
So 'order_header_order_no_seq'
2) Then:
production=# select * from order_header_order_no_seq;
last_value | log_cnt | is_called
+-+---
252 | 0 | t
(1 row)
3) Then in your case:
select max(id) from some_table
Postgres also has UPSERT:
https://www.postgresql.org/docs/12/sql-insert.html
Look for
ON CONFLICT Clause
Though I don't think this is what you need at this point.
Regards
Werner
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Problem with SqlState=23505 when inserting rows
On Wed, Jan 15, 2020 at 9:41 AM Werner Kuhnle wrote: > I've tried to using the newer definition: > id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY > but that does not solve the problem. > > Is there a way to define id columns to that when the database provides > values, > it recognizes already existing values avoiding conflicts. > You'll need to run something like the below to set the next value to the max current value. You'll just have to figure out the name of the sequence that is automatically created whether you use the pseudo type serial, or the newer IDENTITY option. Both are implemented with a sequence. --set sequence to max ID on a table select setval( 'table_name_id_seq', ( select max(id) + 1 from table_name ) );
OID out of range
I am getting ERROR: OID out of range while firing below SQL , what could be the reason? I am joining pg_class and one user_created table to compare tables size. select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as current_time,pg_size_pretty(pg_relation_size(c.oid)) as current_size,pg_size_pretty(u.table_size) as previous_size,pg_size_pretty(pg_relation_size(c.oid) - pg_relation_size(u.table_size)) as diff from user_tables_sizes u join pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r' and c.relnamespace::regnamespace::text='rpx_reporting_stage' and u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of rangetest-# \d user_tables_sizes Table "user_tables_sizes" Column|Type | Collation | Nullable | Default -+-+---+--+- relid | numeric | | | table_size | bigint | | | captured_dt | timestamp without time zone | | | Indexes: "user_tables_sizes_relid_captured_dt_idx" UNIQUE, btree (relid, (captured_dt::date))\d pg_class Table "pg_catalog.pg_class" Column| Type | Collation | Nullable | Default -+--+---+--+- relname | name | | not null | relnamespace| oid | | not null | reltype | oid | | not null | reloftype | oid | | not null | relowner| oid | | not null | relam | oid | | not null | relfilenode | oid | | not null | reltablespace | oid | | not null | relpages| integer | | not null | reltuples | real | | not null | relallvisible | integer | | not null | reltoastrelid | oid | | not null | relhasindex | boolean | | not null | relisshared | boolean | | not null | relpersistence | "char" | | not null | relkind | "char" | | not null | relnatts| smallint | | not null | relchecks | smallint | | not null | relhasoids | boolean | | not null | relhaspkey | boolean | | not null | relhasrules | boolean | | not null | relhastriggers | boolean | | not null | relhassubclass | boolean | | not null | relrowsecurity | boolean | | not null | relforcerowsecurity | boolean | | not null | relispopulated | boolean | | not null | relreplident| "char" | | not null | relispartition | boolean | | not null | relfrozenxid| xid | | not null | relminmxid | xid | | not null | relacl | aclitem[]| | | reloptions | text[] | | | relpartbound| pg_node_tree | | |
Re: OID out of range
On 1/15/20 10:18 AM, Konireddy Rajashekar wrote: I am getting ERROR: OID out of range while firing below SQL , what could be the reason? I am joining pg_class and one user_created table to compare tables size. select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as current_time,pg_size_pretty(pg_relation_size(c.oid)) as current_size,pg_size_pretty(u.table_size) as previous_size,pg_size_pretty(pg_relation_size(c.oid) - pg_relation_size(u.table_size)) as diff from user_tables_sizes u join pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r' and c.relnamespace::regnamespace::text='rpx_reporting_stage' and u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of rangetest-# \d user_tables_sizes Given this: https://www.postgresql.org/docs/12/datatype-oid.html "The oid type is currently implemented as an unsigned four-byte integer. " I am pretty sure this: c.oid::bigint is the problem. -- Adrian Klaver adrian.kla...@aklaver.com
Re: OID out of range
Any workaround to make it work ? On Thu, 16 Jan 2020 at 00:00, Adrian Klaver wrote: > On 1/15/20 10:18 AM, Konireddy Rajashekar wrote: > > I am getting ERROR: OID out of range while firing below SQL , what could > > be the reason? I am joining pg_class and one user_created table to > > compare tables size. > > > > select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as > current_time,pg_size_pretty(pg_relation_size(c.oid)) as > current_size,pg_size_pretty(u.table_size) as > previous_size,pg_size_pretty(pg_relation_size(c.oid) - > pg_relation_size(u.table_size)) as diff from user_tables_sizes u join > pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r' and > c.relnamespace::regnamespace::text='rpx_reporting_stage' and > u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of > rangetest-# \d user_tables_sizes > > Given this: > > https://www.postgresql.org/docs/12/datatype-oid.html > "The oid type is currently implemented as an unsigned four-byte integer. " > > I am pretty sure this: > > c.oid::bigint > > is the problem. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > >
Re: OID out of range
On Wed, Jan 15, 2020 at 11:36 AM bhargav kamineni wrote: > Any workaround to make it work ? > Convert both to text and join on that? Curious choice making relid numeric... David J.
Re: OID out of range
tried casting to text,varchar but no luck select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as current_time,pg_size_pretty(pg_relation_size(c.oid)) as current_size,pg_size_pretty(u.table_size) as previous_size,pg_size_pretty(pg_relation_size(c.oid) - pg_relation_size(u.table_size)) as diff from user_tables_sizes u join pg_class c on u.relid::varchar= c.oid::varchar where c.relkind='r' and c.relnamespace::regnamespace::text='rpx_reporting_stage' and u.captured_dt::date=current_date - interval '1 days' ; ERROR: OID out of range On Thu, Jan 16, 2020 at 12:12 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 15, 2020 at 11:36 AM bhargav kamineni < > bhargavpostg...@gmail.com> wrote: > >> Any workaround to make it work ? >> > > Convert both to text and join on that? Curious choice making relid > numeric... > > David J. >
Re: OID out of range
On 2020-Jan-15, Konireddy Rajashekar wrote: > rangetest-# \d user_tables_sizes > Table "user_tables_sizes" >Column|Type | Collation | Nullable | Default > -+-+---+--+- > relid | numeric | | | Why do you have this column defined as numeric? It seems more sensible to have it as type oid; it's probably that column where you have the out-of-range values. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: OID out of range
@Alvaro Herrera, Can i alter the datatype of relid to oid and try ? On Thu, Jan 16, 2020 at 12:41 AM Alvaro Herrera wrote: > On 2020-Jan-15, Konireddy Rajashekar wrote: > > > rangetest-# \d user_tables_sizes > > Table "user_tables_sizes" > >Column|Type | Collation | Nullable | > Default > > > -+-+---+--+- > > relid | numeric | | | > > Why do you have this column defined as numeric? It seems more sensible > to have it as type oid; it's probably that column where you have the > out-of-range values. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: OID out of range
Please don't top-post here. On Wed, Jan 15, 2020 at 12:01 PM Konireddy Rajashekar < rajkonire...@gmail.com> wrote: > tried casting to text,varchar but no luck > select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as > current_time,pg_size_pretty(pg_relation_size(c.oid)) as > current_size,pg_size_pretty(u.table_size) as > previous_size,pg_size_pretty(pg_relation_size(c.oid) - > pg_relation_size(u.table_size)) as diff from user_tables_sizes u join > pg_class c on u.relid::varchar= c.oid::varchar where c.relkind='r' and > c.relnamespace::regnamespace::text='rpx_reporting_stage' and > u.captured_dt::date=current_date - interval '1 days' > ; > > > ERROR: OID out of range > >> >> Yeah, the join isn't the problem, the error is casting to OID, not from... Pretty sure your problem is: pg_relation_size(u.table_size) David J.
Re: OID out of range
> On Jan 15, 2020, at 12:17 PM, Konireddy Rajashekar > wrote: > > @Alvaro Herrera, Can i alter the datatype of relid to oid and try ? > Not if Alvaro’s theory is correct. List that column, check for nulls, values greater the 2^32.
