Postgres Analog of Oracle APPEND hint
All, Using PostgreSQL 13.1 I am new to PostgreSQL transitioning from Oracle. One of the many Oracle tricks I learned is that large inserts can be sped up by adding the direct path load hint /*+APPEND*/ . I am faced with having to perform many large inserts (100K->100M rows) in my PostgreSQL database. My questions are: - Is there something comparable within the PostgreSQL community edition product? - Are my only options to un-log the table and tune instance memory parameters? I've googled for this and can't find a definitive statement on this point. -- Rumpi Gravenstein
Re: Postgres Analog of Oracle APPEND hint
Unfortunately, I am not looking to load from an external source. My process is moving data from source PostgreSQL tables to target PostgreSQL tables. On Thu, Feb 25, 2021 at 10:36 AM Mark Johnson wrote: > Since INSERT /*+APPEND*/ is generally used when bulk loading data into > Oracle from external files you should probably look at the PostgreSQL COPY > command (https://www.postgresql.org/docs/13/sql-copy.html) and additional > utilities like pg_bulkload (https://github.com/ossc-db/pg_bulkload) . > > On Thu, Feb 25, 2021 at 9:45 AM Rumpi Gravenstein > wrote: > >> All, >> >> Using PostgreSQL 13.1 >> >> I am new to PostgreSQL transitioning from Oracle. One of the many Oracle >> tricks I learned is that large inserts can be sped up by adding the direct >> path load hint /*+APPEND*/ . I am faced with having to perform many large >> inserts (100K->100M rows) in my PostgreSQL database. >> >> My questions are: >> >>- Is there something comparable within the PostgreSQL community >>edition product? >>- Are my only options to un-log the table and tune instance memory >> parameters? >> >> I've googled for this and can't find a definitive statement on this >> point. >> >> -- >> Rumpi Gravenstein >> > -- Rumpi Gravenstein
Re: Postgres Analog of Oracle APPEND hint
My use case involves complicated joins on source tables in one schema loading a target table in the same or a different schema. On Thu, Feb 25, 2021 at 11:41 AM Rob Sargent wrote: > On 2/25/21 9:26 AM, Rumpi Gravenstein wrote: > > Unfortunately, I am not looking to load from an external source. My > > process is moving data from source PostgreSQL tables to target > > PostgreSQL tables. > > > Are you trying to duplicate the source tables in the target tables? If > so, there are replication tools for this. If not, temporary tables in > the target db (possible loaded with copy) and smallish batches of > inserts from those to target might be an option. > > > > -- Rumpi Gravenstein
PostgreSQL Licensing Question for pg_crypto and tablefunc extensions
All, Postgres 13.1 I am new to PostgreSQL and am unclear on how licensing works for PostgreSQL extensions. Are pg_crypto and tablefunc licensed with the PostgreSQL community edition or do PostgreSQL extensions fall under a separate license? I've looked for documentation on this and haven't found anything on-point. Is there a link that describes how each extension is licensed? -- Rumpi Gravenstein
Re: PostgreSQL Licensing Question for pg_crypto and tablefunc extensions
Tom Thanks for the quick reply. What you stated is what I was expecting. I've searched high and low for the documentation that proves that point -- something I need to do to satisfy our legal team. Any thoughts on under which rock that license language exists? Best Regards, Rumpi On Thu, Feb 25, 2021 at 6:26 PM Tom Lane wrote: > Rumpi Gravenstein writes: > > I am new to PostgreSQL and am unclear on how licensing works for > PostgreSQL > > extensions. Are pg_crypto and tablefunc licensed with the PostgreSQL > > community edition or do PostgreSQL extensions fall under a separate > > license? I've looked for documentation on this and haven't found > anything > > on-point. Is there a link that describes how each extension is licensed? > > Everything in contrib/ is considered to be under the same license as the > rest of the distribution. (A few of them have their own copyright text, > but it's not substantially different in meaning from the main copyright > notice. This is also true of bits of the core server, actually.) > > Extensions you get from elsewhere might have different copyrights though. > > regards, tom lane > -- Rumpi Gravenstein
Re: PostgreSQL Licensing Question for pg_crypto and tablefunc extensions
David, Thank you -- that is exactly what I needed! On Fri, Feb 26, 2021 at 2:06 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Feb 26, 2021 at 12:01 PM Adrian Klaver > wrote: > >> Which refers to COPYRIGHT: >> >> >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=COPYRIGHT;h=655a3c59d60f54a824cc8ad6c94a4522f2b465cd;hb=HEAD >> >> > The COPYRIGHT file indeed is serving as the in-repo documentation of our > license. My only gripe is that it fails to explicitly say "The PostgreSQL > License", per [1]. > > As the COPYRIGHT file exists at the top of the repo all source code > underneath, including the contrib directory, is covered. > > [1] https://opensource.org/licenses/postgresql > > David J. > > -- Rumpi Gravenstein
Indexes mysteriously change to LOG
We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit We have recently discovered that on some of our partitioned tables indexes that were created as: CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) somehow changed to include the ON ONLY option: CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, catalog_id) There is no SQL issued that explicitly requests this "ON ONLY" option. I am wondering if this is a side-effect of some other activity. Googling and looking through documentation haven't helped. Does anyone have any thoughts on how this might happen? -- Rumpi Gravenstein
Re: Indexes mysteriously change to ON ONLY
Whoops ... fixed the subject line. On Fri, Jan 27, 2023 at 3:23 PM Rumpi Gravenstein wrote: > We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on > x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat > 8.5.0-10), 64-bit > > We have recently discovered that on some of our partitioned tables indexes > that were created as: > > CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) > > somehow changed to include the ON ONLY option: > > CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, > catalog_id) > > There is no SQL issued that explicitly requests this "ON ONLY" option. I > am wondering if this is a side-effect of some other activity. Googling and > looking through documentation haven't helped. > > Does anyone have any thoughts on how this might happen? > > -- > Rumpi Gravenstein > -- Rumpi Gravenstein
Re: Indexes mysteriously change to LOG
> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) How did you do the above without the table name? That's a cut/paste error. The original index create is: create unique index chapter_u01 on chapter (dur_uk,catalog_id); On Fri, Jan 27, 2023 at 3:34 PM Adrian Klaver wrote: > On 1/27/23 12:23, Rumpi Gravenstein wrote: > > We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > > 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on > > x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat > > 8.5.0-10), 64-bit > > > > We have recently discovered that on some of our partitioned tables > > indexes that were created as: > > > > CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) > > How did you do the above without the table name? > > > > > somehow changed to include the ON ONLY option: > > > > CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, > > catalog_id) > > > > There is no SQL issued that explicitly requests this "ON ONLY" option. > > I am wondering if this is a side-effect of some other activity. > > Googling and looking through documentation haven't helped. > > > > Does anyone have any thoughts on how this might happen? > > > > -- > > Rumpi Gravenstein > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Rumpi Gravenstein
Re: Indexes mysteriously change to ON ONLY
We are using the pg_indexes view (indexdef) to retrieve the index definition. Are you saying that as a normal part of building an index, there are short periods of time where the pg_indexes view will show the index with ON ONLY specified? On Fri, Jan 27, 2023 at 3:53 PM Tom Lane wrote: > Rumpi Gravenstein writes: > >> We have recently discovered that on some of our partitioned tables > indexes > >> that were created as: > >> > >> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) > >> > >> somehow changed to include the ON ONLY option: > >> > >> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, > >> catalog_id) > > What do you mean "somehow changed"? There is nothing in the system > catalogs that stores that exact string, so I suppose what you mean > is that some tool is presenting the indexes to you that way. > > If that tool is pg_dump, this is its normal behavior. There will > be other commands in its output that build the rest of the > partitioned index set. > > regards, tom lane > -- Rumpi Gravenstein
Re: Indexes mysteriously change to ON ONLY
Tom/Christophe I now understand. Thanks for the clear explanation. On Fri, Jan 27, 2023 at 4:16 PM Tom Lane wrote: > Rumpi Gravenstein writes: > > We are using the pg_indexes view (indexdef) to retrieve the index > > definition. > > Ah. > > > Are you saying that as a normal part of building an index, there are > short > > periods of time where the pg_indexes view will show the index with ON > ONLY > > specified? > > No, there's no "short periods", this is what it shows. That's partly > because the output is designed for pg_dump to use. But there's > a reasonably good argument for it anyway, which is that if you just > say "create index" then that's effectively a macro for building the > whole partitioned index set. That pg_indexes entry is only about the > top-level "virtual" index, and there are other entries for the leaf > indexes. For example, > > regression=# create table foo (f1 int primary key) partition by list (f1); > CREATE TABLE > regression=# create table foo_1 partition of foo for values in (1); > CREATE TABLE > regression=# create table foo_2 partition of foo for values in (2); > CREATE TABLE > regression=# select tablename,indexname,indexdef from pg_indexes where > indexname like 'foo%'; > tablename | indexname | indexdef > > > ---++-- > foo | foo_pkey | CREATE UNIQUE INDEX foo_pkey ON ONLY public.foo > USING btree (f1) > foo_1 | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON public.foo_1 > USING btree (f1) > foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2 > USING btree (f1) > (3 rows) > > If you wanted to reconstruct this from individual parts, as pg_dump does, > you'd issue those commands and then connect them together with ATTACH > PARTITION commands. > > regards, tom lane > -- Rumpi Gravenstein
Looking for PostgreSQL Tuning Workshop Presenter
Hi, I am the coordinator for a Cleveland Ohio user group interested in bringing in a presenter to do a deep dive on PostgreSQL plans and tuning. I'm relatively new to the community so don't know who to ask or who might be interested. If you know of someone that has deep experience and would be willing to come to Cleveland, I'd be interested in discussing this opportunity with them. Best Regards, -- Rumpi Gravenstein
Analytic Function Bug
Experts,
I am running on
PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
20210514 (Red Hat 8.5.0-22), 64-bit
I have the following query which returns what I expect:
with
d(logical_partition_key, model_usage) as ( values( 'TEST_DATA' ,
'F(T61)(EXPORT)' )
)
,
usg_txt as (
SELECT DISTINCT logical_partition_key,
MODEL_USAGE as usage_text,
REPLACE ( REPLACE ( REPLACE ( REPLACE ( PIU.MODEL_USAGE::text, '', ''), '', ''), '', ''), '', '') AS txt
FROM d /*CAO_CALLOUT_GHOST_COMB_W*/ piu
)
,
parse( logical_partition_key, usage_text,txt, rpo_txt, indx ) as
(
select d.logical_partition_key, d.usage_text,d.txt, coalesce(a.rpo[1],a.rpo[
2]) as rpo_txt, a.pos
from usg_txt d
left join lateral regexp_matches( txt, '([ ,\-()/&])|([^ ,\-()/&]+)','g')
with ordinality as a(rpo,pos) on true
)
,
prv_nxt_token( logical_partition_key, usage_text,txt, rpo_txt, indx,
mx_indx, prev,nxt,nxt2, prv2,prv3) as
(
/* Get prior and next token to support later logic */
select p.logical_partition_key, p.usage_text,
p.txt, p.rpo_txt, indx,
max( indx) over ( partition by p.txt ) mx_indx,
lag( p.rpo_txt,1 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prev,
lag( p.rpo_txt,-1 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) nxt,
lag( p.rpo_txt,-2) over ( partition by p.logical_partition_key,p.txt order
by indx ) nxt2,
lag( p.rpo_txt,2 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prv2,
lag( p.rpo_txt,3 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prv3
from parse p
)
select * from prv_nxt_token;
logical_partition_keyusage_texttxtrpo_txtindxmx_indxprevnxtnxt2prv2prv3
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "F" "1" "7" "[NULL]" "(" "T61"
"[NULL]" "[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "2" "7" "F" "T61" ")"
"[NULL]" "[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "T61" "3" "7" "(" ")" "(" "F"
"[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "4" "7" "T61" "(" "EXPORT"
"(" "F"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "5" "7" ")" "EXPORT" ")"
"T61" "("
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "EXPORT" "6" "7" "(" ")"
"[NULL]" ")" "T61"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "7" "7" "EXPORT" "[NULL]"
"[NULL]" "(" ")"
When I run the same statement, except this time using a large table instead
of a values statement, I get the wrong answer. The difference in the SQL
statement that produced the following data is that the large table (10M
records) and there is a closing where condition used to limit the result to
what is shown:
select * from prv_nxt_token;
is replaced by:
select * from prv_nxt_token where logical_partition_key='TEST_DATA' and
usage_text='F(T61)(EXPORT)';
Which returns:
logical_partition_keyusage_texttxtrpo_txtindxmx_indxprvnxtnxt2prv2prv3
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" "F" "1" "7" *"F"* "(" "(" "[NULL]"
"[NULL]"
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "2" "7" "F" "(" "T61" "F" "[NULL]"
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" "T61" "3" "7" *"T61"* ")" ")" "(" "("
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "4" "7" "T61" ")" "(" "T61" "("
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "5" "7" "*("* "EXPORT" "EXPORT" ")"
")"
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" "EXPORT" "6" "7" "(" "EXPORT" ")" "(" ")"
"TEST_DATA"
"F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "7" "7" ")" "[NULL]" "[NULL]" "EXPORT"
"EXPORT"
Notice that the prv column (lag - 1 ) is just wrong. I've highlighted
obvious bad values. Other columns are wrong as well.
Is this a PostgreSQL bug?
--
Rumpi Gravenstein
Re: Analytic Function Bug
that the lag() functions are seeing some rows that don't show up in the final output. I'm under the impression that the predicate filter is applied before the analytic is evaluated. Are you suggesting that I have this wrong -- the analytic is evaluated and then the filter is applied? On Thu, Aug 29, 2024 at 8:07 PM Tom Lane wrote: > Rumpi Gravenstein writes: > > Is this a PostgreSQL bug? > > Maybe, but you haven't provided enough information to let anyone else > reproduce the behavior. > > Personally I'm suspicious that because your lag() calls are over > > partition by p.logical_partition_key, p.txt order by indx > > but then you filter by > > where logical_partition_key='TEST_DATA' and > usage_text='F(T61)(EXPORT)'; > > that the lag() functions are seeing some rows that don't show up in > the final output. (This'd require that some output rows from "parse" > share txt values but not usage_text values, but that certainly looks > like it's possible.) So IMO you have not actually demonstrated that > there is any bug. > > regards, tom lane > -- Rumpi Gravenstein
Re: Analytic Function Bug
Wait...I see my issue. Duh! The where clause is applied after the CTE is evaluated On Fri, Aug 30, 2024 at 7:37 AM Rumpi Gravenstein wrote: > > that the lag() functions are seeing some rows that don't show up in > the final output. > > > I'm under the impression that the predicate filter is applied before the > analytic is evaluated. Are you suggesting that I have this wrong -- the > analytic is evaluated and then the filter is applied? > > On Thu, Aug 29, 2024 at 8:07 PM Tom Lane wrote: > >> Rumpi Gravenstein writes: >> > Is this a PostgreSQL bug? >> >> Maybe, but you haven't provided enough information to let anyone else >> reproduce the behavior. >> >> Personally I'm suspicious that because your lag() calls are over >> >> partition by p.logical_partition_key, p.txt order by indx >> >> but then you filter by >> >> where logical_partition_key='TEST_DATA' and >> usage_text='F(T61)(EXPORT)'; >> >> that the lag() functions are seeing some rows that don't show up in >> the final output. (This'd require that some output rows from "parse" >> share txt values but not usage_text values, but that certainly looks >> like it's possible.) So IMO you have not actually demonstrated that >> there is any bug. >> >> regards, tom lane >> > > > -- > Rumpi Gravenstein > -- Rumpi Gravenstein
Partitioned Table Index Column Order
All, I'm on PostgreSQL 13 and have a partitioned table with a primary key. create table t( a integer, b integer, c varchar, d .. ) partitioned by range( a ); As a best practice is it better to create the primary key starting or ending with the partition column? e.g. 1) t_pkey primary key (a, b, c) or 2) t_pkey primary key (b, c, a) Neither the PostgreSQL documentation nor Google have an answer - at least as far as I could find. I see examples in the Postgres documentation that use 2) but no explanation of why that choice was made. Does it even make a difference? Thoughts? -- Rumpi Gravenstein
