Table partitioning with sequence field in postgresql12

On Thu, Jun 18, 2020 at 12:00 PM Srinivasa T N <seenutn@gmail.com> wrote:

Hi,
Partitioning of a table with sequence id as one of its fields is supported in postgresql12?

Could you please elaborate on your case a bit more?

Regards,
Amul

Hi,
I have a parent table with one of the field as ""gid" int4 DEFAULT
nextval('"ami_smart_new".aoi_boundary_gid_seq'::regclass)".

I create child tables which inherit parent and use hash partition. When
I directly insert into child tables, will there be any race condition
causing two child tables getting the same sequence value for gid?

Regards,
Seenu.

On Thu, Jun 18, 2020 at 12:09 PM amul sul <sulamul@gmail.com> wrote:

Srinivasa T N <seenutn@gmail.com> writes:

Hi,
Partitioning of a table with sequence id as one of its fields is
supported in postgresql12?

Regards,
Seenu.

A sequence is really just an 'atomic' number generator, you get the next
value, which is guaranteed to be larger than the last 'nextval' (up
until maxvalue). It is unaware of the use i.e. whether it will be used
in a insert or what table that insert is against. So I'm not sure what
your concern with a partitioned table is? Can you elaborate?

--
Tim Cross

On Thu, Jun 18, 2020 at 12:22 PM Srinivasa T N <seenutn@gmail.com> wrote:

Hi,
I have a parent table with one of the field as ""gid" int4 DEFAULT nextval('"ami_smart_new".aoi_boundary_gid_seq'::regclass)".

I create child tables which inherit parent and use hash partition. When I directly insert into child tables, will there be any race condition causing two child tables getting the same sequence value for gid?

if "gid" is the partitioning key and assuming you are using declarative
partitioning[1], then it won't be possible to have the same "gid" value in two
child partitions.

1] https://www.postgresql.org/docs/current/ddl-partitioning.html

regards,
Amul

I am using declarative partitioning on some other field of the same table
in which gid also exists.

Regards,
Seenu.

On Thu, Jun 18, 2020 at 12:31 PM amul sul <sulamul@gmail.com> wrote:

Srinivasa T N <seenutn@gmail.com> writes:

Hi,
I have a parent table with one of the field as ""gid" int4 DEFAULT
nextval('"ami_smart_new".aoi_boundary_gid_seq'::regclass)".

I create child tables which inherit parent and use hash partition. When
I directly insert into child tables, will there be any race condition
causing two child tables getting the same sequence value for gid?

Assuming all inserts use the default e.g. nextval from the same
sequence, you won't get duplicates. You could get a duplicate if an
insert sets an explicit value for gid of course or if rows in any table
were inserted with a gid which was not obtained from the same sequence
using nextval i.e. parent and children use same sequence. The sequence
is just a counter with the property that no two calls to nextval from
that sequence will have the same value. You cannot make any additional
assumptions e.g. cannot assume gid values will be inserted in order or
there won't be 'gaps ' etc.
--
Tim Cross