pg_stat_user_tables.n_tup_ins empty for partitioned table

[Luca Ferrari]

Hi all,
I've got a table named "root", partitioned on a date field into years
(e.g., "2018") and into months like "y2018m11" using range
partitioning on PostgreSQL 11.2.
Tuples are inserted into root with an INSERT...SELECT. I have
performed an UPDATE due to a new column inserted in the parent table
y2018.

Today, checking the statistics of the table, I found that no tuples
were inserted:

testdb=> SELECT relname, seq_scan, idx_scan,
 n_tup_ins, n_tup_del, n_tup_upd, n_tup_hot_upd,
 n_live_tup, n_dead_tup,
 last_vacuum, last_autovacuum,
 last_analyze, last_autoanalyze
 FROM pg_stat_user_tables;

-[ RECORD 12 ]---+--
relname  | y2018m11
seq_scan | 42172
idx_scan |
n_tup_ins| 0
n_tup_del| 0
n_tup_upd| 28191462
n_tup_hot_upd| 86
n_live_tup   | 14086279
n_dead_tup   | 0
last_vacuum  |
last_autovacuum  | 2019-03-28 17:23:35.909943+01
last_analyze |
last_autoanalyze | 2019-03-28 17:25:12.773707+01


I suspect the fact that n_tup_ins is 0 is due to the fact that I did
insert the tuples into the parent y2018, but I would like to better
understand: how can have n_live_tup without having n_tup_ins?
Moreover, I don't have any stat for the parent table root, neither for
y2018.
Some explaination/pointer is appreciated.

Thanks,
Luca

Re: distinguish update from insert (on conflict)

[Fabio Ugo Venchiarutti]

On 22/05/2019 03:37, Justin Pryzby wrote:

On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:

On 5/21/19 6:34 PM, Justin Pryzby wrote:

Is it still impossible to distinguish whether a row was inserted vs updated ?


You will need to be more specific.


Sorry, I mean with UPSERT / "INSERT .. ON CONFLICT DO UPDATE", is it possible
to tell whether a row was inserted vs. updated ?

Thanks,
Justin




Here's my recipe for that:

RETURNING
/* whatever, */
(xmax = 0) AS is_new_record
;

I don't know if any of the hackers thought of a sleeker technique



--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice:  This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group. 


 

If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses. 


 

Fetch and Sizzle 
are trading names of Speciality Stores Limited and Fabled is a trading name 
of Marie Claire Beauty Limited, both members of the Ocado Group.


 


References to the “Ocado Group” are to Ocado Group plc (registered in 
England and Wales with number 7098618) and its subsidiary undertakings (as 
that expression is defined in the Companies Act 2006) from time to time.  
The registered office of Ocado Group plc is Buildings One & Two, Trident 
Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.

Re: Bulk inserts into two (related) tables

[Francisco Olarte]

On Tue, May 21, 2019 at 7:24 PM Rich Shepard  wrote:
> > From how you say it, I assume you have some data in your original
> > dumps which can relate boths, lets assume it's org_name, but may be an
> > org-code. If you do not have it it means you cannot match people to
> > orgs in your data, all is lost.
> Not yet with these new data.
> I'll manually insert the org_id numbers from the organizations table into
> the people table.

I'm curious, what org_id do you put (manually) to the people? you must
have some way to match it ( like, say, "I have an organization line,
followed by lines for people in this organization"

Because if you cannot match them by hand, the Db is not going to be of use.

Knowing how you know the org for a people is crucial for a solution (
i.e., for the previous example I'll just add a synthetic org_code line
to each one and do the to temp tables trick for a long file, or, just
advance the sequence (if needed manually ( to reserve org_id )) and
them do a perl one liner to augment the data ( but I've been perling
since the 4.019 times, so this may not be as easy for others ).

Francisco Olarte.

Re: Bulk inserts into two (related) tables

[Francisco Olarte]

Jeremy:

On Tue, May 21, 2019 at 11:58 PM Jeremy Finzel  wrote:

> Then take Francisco's suggestion, only use an md5 of the organization fields 
> to create yourself a unique identifier.  Then you can use ctid (unique 
> internal identifier for each row) to join back.  You use SQL like this:

Sadly my suggestion only works if you can ( manually ) assign an
organization line to a people line, md5, field concatenation,
everything else is just optimization.

>From what the OP has already told you have a heap of people, a heap of
organizations and a magic device to assign one to the others, the
org_id assignment ( using an unknown algorithm, we do not know if he
wants sequences, texts or cat gifs as IDs ) is easy, the pairing part
is unsolvable with the data we have.

At this moment I think the only useful link for this is
http://xyproblem.info/ ( for the OP, not for U ).

Francisco Olarte.

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Francisco Olarte wrote:


I'm curious, what org_id do you put (manually) to the people? you must
have some way to match it ( like, say, "I have an organization line,
followed by lines for people in this organization"


Francisco,

The first data entered was in small chunks so I'd add a few rows to the
organization table, output a file of org_id and org_name, then use the
source data to associate that org_id to the people associated with it.

With this new data source I want to populate the two tables more quickly. I
think the time consuming part is associating people with their organization.
I'll do more thinking about this.

I don't do perl, though.

Thanks,

Rich

how to write correctly this update ?

[Pierre Couderc]

I have tried many ways to update a table  :

UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, 
personnes T3

WHERE  ;


UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, 
personnes T2, personnes T3

WHERE ..;


In my case , where clause is : WHERE T1.id=T2.id_mere AND 
T2.id_pere=T3.id AND T1.nom != T3.nom;


Thanks.

PC

Re: Bulk inserts into two (related) tables

[Jeremy Finzel]

On Wed, May 22, 2019 at 7:40 AM Rich Shepard 
wrote:

> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > I'm curious, what org_id do you put (manually) to the people? you must
> > have some way to match it ( like, say, "I have an organization line,
> > followed by lines for people in this organization"
>
> Francisco,
>
> The first data entered was in small chunks so I'd add a few rows to the
> organization table, output a file of org_id and org_name, then use the
> source data to associate that org_id to the people associated with it.
>
> With this new data source I want to populate the two tables more quickly. I
> think the time consuming part is associating people with their
> organization.
> I'll do more thinking about this.
>
> I don't do perl, though.
>

There's absolutely no need to use anything beyond SQL here, though you
could if you want to.

I really wonder how much we are just talking past each other simply because
we don't know what your data looks like, so we can't show you how our
examples apply to your use case.  If you provided a sample scrubbed data
file, this whole thread probably would have been much shorter :).  Can you
do that?

You said here again the most time consuming part is associating people with
their organization.  Well, that's the whole question I was trying to
optimize on.  You told Francisco that the data file does not have a unique
org name that could be used as a unique organization identifier.  However
you seem to have contradicted that by responding favorably to this solution:

INSERT INTO PEOPLE (id, name, org_id) VALUES (1, ‘Bob’, (SELECT org_id FROM
org WHERE org_name=‘Main Office’))

How can this solution work if you said org_name cannot be used to link a
person in the data file?

So, the question again becomes: does your data file have a combination of
org fields that allows you to assign a person to a unique organization?

   - If the answer is still no, how could you ever assign people to a
   unique organization?  In that case you need to massage your data file first
   before trying to build a database schema, as Francisco noted.  This is
   basically a non-starter for your database schema.
   - If the answer is yes, that means you can use the combination of those
   fields to uniquely identify an organization, and thus link people to it
   later.  That's the reason for the md5 - to easily use many fields in
   combination as a unique id

Thanks,
Jeremy

Re: how to write correctly this update ?

[Rob Sargent]

Don’t use the alias on the column(s) being set.
This passed the parser:
UPDATE  personnes T1 SET nom_naiss=T1.nom FROM  personnes T2, personnes T3
WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;

(I have no data in my table so I can’t confirm the logic.  You seem to want to 
update mother’s maiden name if her current (sur)name doesn’t match that of the 
father of (one of?) her children?  That might be a serious leap of faith.)

> On May 22, 2019, at 6:43 AM, Pierre Couderc  wrote:
> 
> I have tried many ways to update a table  :
> 
> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, personnes T3
> WHERE  ;
> 
> 
> UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, personnes 
> T2, personnes T3
> WHERE ..;
> 
> 
> In my case , where clause is : WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id 
> AND T1.nom != T3.nom;
> 
> Thanks.
> 
> PC
> 
> 
> 
> 
> 
> 

Which records aren't in list? Use static list as table or records

[Durumdara]

Hi!

A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.

The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)

For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
select id from idlist
where id not in (select id from theserecords)

The id list is a static string.

Now I can do this with temporary table - I create one, insert the ID-s and
run the select:

select id from temptable where id not in (select id from theserecords)

It would be nice if I can get the missing ID-s.

F.e:

select id from (
   select 1 as id,
   select 5 as id,
   ...
) where id not in (select id from theserecords)

or

select id from (
  select split_string_to_records('1,2,3,4,5', ',') as id
) ...

Do you know any simple way to do this without stored proc or temp table?

Thank you!

B.W:
dd

SV: Which records aren't in list? Use static list as table or records

[Gustavsson Mikael]

Hi,

You can use generate_series.

select generate_series(1,select max(id) from theserecords)
EXCEPT
select id from theserecords;

KR

Från: Durumdara [durumd...@gmail.com]
Skickat: den 22 maj 2019 15:43
Till: Postgres General
Ämne: Which records aren't in list? Use static list as table or records

Hi!

A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.

The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)

For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
select id from idlist
where id not in (select id from theserecords)

The id list is a static string.

Now I can do this with temporary table - I create one, insert the ID-s and run 
the select:

select id from temptable where id not in (select id from theserecords)

It would be nice if I can get the missing ID-s.

F.e:

select id from (
   select 1 as id,
   select 5 as id,
   ...
) where id not in (select id from theserecords)

or

select id from (
  select split_string_to_records('1,2,3,4,5', ',') as id
) ...

Do you know any simple way to do this without stored proc or temp table?

Thank you!

B.W:
dd

SV: Which records aren't in list? Use static list as table or records

[Gustavsson Mikael]

Here is one without syntax error.

select generate_series(1,(select max(id) from theserecords))
EXCEPT
select id from theserecords;


Från: Gustavsson Mikael [mikael.gustavs...@smhi.se]
Skickat: den 22 maj 2019 15:51
Till: Durumdara; Postgres General
Ämne: SV: Which records aren't in list? Use static list as table or records

Hi,

You can use generate_series.

select generate_series(1,select max(id) from theserecords)
EXCEPT
select id from theserecords;

KR

Från: Durumdara [durumd...@gmail.com]
Skickat: den 22 maj 2019 15:43
Till: Postgres General
Ämne: Which records aren't in list? Use static list as table or records

Hi!

A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.

The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)

For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
select id from idlist
where id not in (select id from theserecords)

The id list is a static string.

Now I can do this with temporary table - I create one, insert the ID-s and run 
the select:

select id from temptable where id not in (select id from theserecords)

It would be nice if I can get the missing ID-s.

F.e:

select id from (
   select 1 as id,
   select 5 as id,
   ...
) where id not in (select id from theserecords)

or

select id from (
  select split_string_to_records('1,2,3,4,5', ',') as id
) ...

Do you know any simple way to do this without stored proc or temp table?

Thank you!

B.W:
dd

RE: Which records aren't in list? Use static list as table or records

[Patrick FICHE]

Hi,

May be something like this could help

SELECT TestList.id
FROM ( SELECT * FROM ( VALUES( 1 ), (5), (12), (33), (55) ) t ) AS TestList( id 
)
LEFT OUTER JOIN idList ON IdList.id = TEstList.id
WHERE IdList.Id IS NULL;


Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96

[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]

From: Durumdara 
Sent: Wednesday, May 22, 2019 3:43 PM
To: Postgres General 
Subject: Which records aren't in list? Use static list as table or records

Hi!

A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.

The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)

For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
select id from idlist
where id not in (select id from theserecords)

The id list is a static string.

Now I can do this with temporary table - I create one, insert the ID-s and run 
the select:

select id from temptable where id not in (select id from theserecords)

It would be nice if I can get the missing ID-s.

F.e:

select id from (
   select 1 as id,
   select 5 as id,
   ...
) where id not in (select id from theserecords)

or
select id from (
  select split_string_to_records('1,2,3,4,5', ',') as id
) ...

Do you know any simple way to do this without stored proc or temp table?

Thank you!

B.W:
dd

Re: how to write correctly this update ?

[Pierre Couderc]

On 5/22/19 3:19 PM, Rob Sargent wrote:

Don’t use the alias on the column(s) being set.
This passed the parser:
UPDATE  personnes T1 SET nom_naiss=T1.nom FROM  personnes T2, personnes T3
WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;

Wow, fine ! you got  it!


(I have no data in my table so I can’t confirm the logic.  You seem to want to 
update mother’s maiden name if her current (sur)name doesn’t match that of the 
father of (one of?) her children?  That might be a serious leap of faith.)

Now I check manually... ;)



On May 22, 2019, at 6:43 AM, Pierre Couderc  wrote:

I have tried many ways to update a table  :

UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T2, personnes T3
WHERE  ;


UPDATE  personnes T1 SET T1.nom_naiss=T1.nom FROM  personnes T1, personnes T2, 
personnes T3
WHERE ..;


In my case , where clause is : WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND 
T1.nom != T3.nom;

Thanks.

PC

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Jeremy Finzel wrote:


There's absolutely no need to use anything beyond SQL here, though you
could if you want to.


Jeremy,

This is a new experience for me so I didn't think of a SQL solution.


I really wonder how much we are just talking past each other simply because
we don't know what your data looks like, so we can't show you how our
examples apply to your use case. If you provided a sample scrubbed data
file, this whole thread probably would have been much shorter :).  Can you
do that?


Not necessary; see below. Also, these data come from a regulator and
provided as an Excel spreadsheet. If they were extracted from a database
then that was very poorly designed because there's no consistency in how
fields/columns are formatted. This requires manual cleaning.

Each row in the source file (exported from the spreadsheet as .csv and
renamed to .txt for processing in emacs and awk) is a mixture of attributes
that belong in either or both of the organization and people tables in my
database. An awk script will extract the appropriate fields for each table.


You told Francisco that the data file does not have a unique org name that
could be used as a unique organization identifier. However you seem to
have contradicted that by responding favorably to this solution:


The org_name is not the PK; the org_id is. This was assigned by postgres
when the original rows were inserted. Now, I can add the org_id in the
values to be inserted as I know the maximum org_id number in that table.


INSERT INTO PEOPLE (id, name, org_id) VALUES (1, ‘Bob’, (SELECT org_id FROM
org WHERE org_name=‘Main Office’))


Question: do I use this same syntax for each row to be inserted or can I
make it one long insert statement by separating the parenthesized values
with commas as I do when I update multiple rows in a table?

Thanks very much,

Rich

Re: distinguish update from insert (on conflict)

[Adrian Klaver]

On 5/21/19 7:37 PM, Justin Pryzby wrote:

On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:

On 5/21/19 6:34 PM, Justin Pryzby wrote:

Is it still impossible to distinguish whether a row was inserted vs updated ?


You will need to be more specific.


Sorry, I mean with UPSERT / "INSERT .. ON CONFLICT DO UPDATE", is it possible
to tell whether a row was inserted vs. updated ?


In addition to Fabio's suggestion, from my previous post:

https://www.postgresql.org/docs/10/sql-createtrigger.html

"n some cases it is possible for a single SQL command to fire more than 
one kind of trigger. For instance an INSERT with an ON CONFLICT DO 
UPDATE clause may cause both insert and update operations, so it will 
fire both kinds of triggers as needed. The transition relations supplied 
to triggers are specific to their event type; thus an INSERT trigger 
will see only the inserted rows, while an UPDATE trigger will see only 
the updated rows."


So you might want to check out triggers using transition tables.



Thanks,
Justin




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Bulk inserts into two (related) tables

[Rob Sargent]

> 
> Each row in the source file (exported from the spreadsheet as .csv and
> renamed to .txt for processing in emacs and awk) is a mixture of attributes
Absolutely no need to rename the .csv for those tools.
> that belong in either or both of the organization and people tables in my
> database. An awk script will extract the appropriate fields for each table.

Re: Bulk inserts into two (related) tables

[Adrian Klaver]

On 5/22/19 7:38 AM, Rich Shepard wrote:

On Wed, 22 May 2019, Jeremy Finzel wrote:


There's absolutely no need to use anything beyond SQL here, though you
could if you want to.


Jeremy,

This is a new experience for me so I didn't think of a SQL solution.

I really wonder how much we are just talking past each other simply 
because

we don't know what your data looks like, so we can't show you how our
examples apply to your use case. If you provided a sample scrubbed data
file, this whole thread probably would have been much shorter :).  Can 
you

do that?


Not necessary; see below. Also, these data come from a regulator and
provided as an Excel spreadsheet. If they were extracted from a database
then that was very poorly designed because there's no consistency in how
fields/columns are formatted. This requires manual cleaning.

Each row in the source file (exported from the spreadsheet as .csv and
renamed to .txt for processing in emacs and awk) is a mixture of attributes
that belong in either or both of the organization and people tables in my
database. An awk script will extract the appropriate fields for each table.


So does the people data have an organization attribute?

If so why not just assign the org_id while cleaning up the data?



You told Francisco that the data file does not have a unique org name 
that

could be used as a unique organization identifier. However you seem to
have contradicted that by responding favorably to this solution:


The org_name is not the PK; the org_id is. This was assigned by postgres
when the original rows were inserted. Now, I can add the org_id in the
values to be inserted as I know the maximum org_id number in that table.

INSERT INTO PEOPLE (id, name, org_id) VALUES (1, ‘Bob’, (SELECT org_id 
FROM

org WHERE org_name=‘Main Office’))


Question: do I use this same syntax for each row to be inserted or can I
make it one long insert statement by separating the parenthesized values
with commas as I do when I update multiple rows in a table?

Thanks very much,

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Adrian Klaver wrote:


So does the people data have an organization attribute?


Adrian,

Yes. It's the FK to the organization table.


If so why not just assign the org_id while cleaning up the data?


That's what I thought to do based on your suggestion yesterday. It would
make life simpler.

Regards,

Rich

Re: Bulk inserts into two (related) tables

[Francisco Olarte]

Rich:

On Wed, May 22, 2019 at 2:40 PM Rich Shepard  wrote:
> > I'm curious, what org_id do you put (manually) to the people? you must
> > have some way to match it ( like, say, "I have an organization line,
> > followed by lines for people in this organization"

> The first data entered was in small chunks so I'd add a few rows to the
> organization table, output a file of org_id and org_name, then use the
> source data to associate that org_id to the people associated with it.

This is my point, if you explain how you "use the source data to
assiociate that org_id to the people" I ( the list in general ) MAY be
able to suggest something to help you.

But, I do not know how you do that association step. You may have the
them in an original directory, may have it commited to memory, may
have people typed in green cards attached with red strings to
organizations carved in wooden blocks. And this information is
crucial.

> With this new data source I want to populate the two tables more quickly. I
> think the time consuming part is associating people with their organization.
> I'll do more thinking about this.

Some sample lines may be useful, even with made up data, structure is
what is important. I.e., something like
>>
O "clavelitos locos" 24 37 82
O "bits cuadrados" 36 0 27
P "Oscar Ortega", 46, "manager", "clavelitos locos"
P "Abel Alonso", 37, "boss", "bits cuadrados"
P "Pepe Perez", 52, null, "clavelitos locos"
<<
>From this ( and a description of the type of ids in your tables, i.e.,
are they sequences? texts? hashes? uuids? ) I could suggest something
like "put them into two temp tables, add org_id to Os, join in the
appropiate column to put org_id in P, slice them with a select and
send data to final table".

Or, from something like this:
>>
clavelitos_locos 24 37 82
 Pepe_Perez 52 \N
 Oscar_Ortega 46 manager
bits_cuadrados 36 0 27
  Abel_Alonso 37 boss
<<
( I've used single spaces instead of the usual tabs for separators in
the example )
I could suggest "pipe it through this, use your start id instead of
1200": perl -ne 'BEGIN{$o=1200}; if (/^\s/) { print STDERR "$o$_"; }
else { ++$o; print "$o $_"; }' > orgs 2>people
( I would add a little explaining, in case it sounds like line noise )
Which will give you:
>>
0:~$ fgrep '' orgs people
orgs:1201 clavelitos_locos 24 37 82
orgs:1202 bits_cuadrados 36 0 27
people:1201 Pepe_Perez 52 \N
people:1201 Oscar_Ortega 46 manager
people:1202  Abel_Alonso 37 boss
<<
Which could easily be copied in, perhaps with an intermediate table
for a little clean up.



That's why I pointed to xyproblem, I think you are not stating your
problem correctly. I've done thousands of things like this over the
years, I they are normally not that hard.

Regards.
   Francisco Olarte.











>
> I don't do perl, though.
>
> Thanks,
>
> Rich
>
>

Re: Bulk inserts into two (related) tables

[Francisco Olarte]

Rich:

On Wed, May 22, 2019 at 4:38 PM Rich Shepard  wrote:

> Not necessary; see below. Also, these data come from a regulator and
> provided as an Excel spreadsheet. If they were extracted from a database
> then that was very poorly designed because there's no consistency in how
> fields/columns are formatted. This requires manual cleaning.

Welcome to my world! I routinely ( dayly / weekly ) receive things
which I know are in a database but they decide to pass through excel (
a program well know in the data base comunity for eating data, as it
does not always store what it gets, I've managed to make excel export
a sheet to csv, import it, reexport it and get a (noticeabley)
different csv ). Or to denormalize. Or they send me files with a
mixture of utf-8 and latin1 ( in the same file ). Some of my importing
programs could qualify as IA.

> Each row in the source file (exported from the spreadsheet as .csv and
> renamed to .txt for processing in emacs and awk) is a mixture of attributes
> that belong in either or both of the organization and people tables in my
> database. An awk script will extract the appropriate fields for each table.

Now we know a bit.

> > You told Francisco that the data file does not have a unique org name that
> > could be used as a unique organization identifier. However you seem to
> > have contradicted that by responding favorably to this solution:
> The org_name is not the PK; the org_id is. This was assigned by postgres
> when the original rows were inserted. Now, I can add the org_id in the
> values to be inserted as I know the maximum org_id number in that table.

You are not reading what we write to you. Note YOU AND ONLY YOU are
the one speaking of PK. We are speaking of "unique identifier" ( that
would be, IIRC, "candidate keys", you can peek any as your PK, or even
introduce a new synthetic one with a sequence, or a femto second exact
timestamp or whatever ).


> > INSERT INTO PEOPLE (id, name, org_id) VALUES (1, ‘Bob’, (SELECT org_id FROM
> > org WHERE org_name=‘Main Office’))
> Question: do I use this same syntax for each row to be inserted or can I
> make it one long insert statement by separating the parenthesized values
> with commas as I do when I update multiple rows in a table?

If this works you do neither. You insert 'Main Office' as an extra
column in a TEMPORARY table and the n you do some thing like:

INSERT INTO PEOPLE (id, name, org_id ) (SELECT tp.id, tp.name,
o.org_id FROM temp_people tp , organizations o WHERE o.org_name =
tp.extra_column_for_org_name).

and drop the temporaty table after it.

When you are fluent in SQL you do not try to play with files, you
import every column of your data into temporary tables, clean them up,
and join ( if needed ) them until you have a select that gives you
what you want and then insert this. Normally you insert several
SELECTS into temporary tables ( specially when you only have thousands
of records ) so you can do the clean up in steps.

Francisco Olarte.

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Francisco Olarte wrote:


You are not reading what we write to you. Note YOU AND ONLY YOU are the
one speaking of PK. We are speaking of "unique identifier" ( that would
be, IIRC, "candidate keys", you can peek any as your PK, or even introduce
a new synthetic one with a sequence, or a femto second exact timestamp or
whatever ).


Francisco,

Let me clarify.

The organizations table has org_id (an integer) as PK.

The people table has person_id (an interger) as PK and org_id as the
reference to organization.org_id.

Does this help?


When you are fluent in SQL you do not try to play with files, you import
every column of your data into temporary tables, clean them up, and join (
if needed ) them until you have a select that gives you what you want and
then insert this. Normally you insert several SELECTS into temporary
tables ( specially when you only have thousands of records ) so you can do
the clean up in steps.


Most of my time is spent writing using LaTeX/LyX. Depending on the project's
needs I'll also use SQL, R, GRASS, and other tools. I'm a generalist, like
your PCP, not a specialist. But, I also rely on emacs, grep, sed, and awk
for data munging and am more fluent with these tools than I am with SQL or
Python.

For me, the quickest and simplest appoach is to add the PKs to each table,
and the org_id into the people table, when I separate the cleaned text file
into the columns for each table.

Regards,

Rich

Re: Bulk inserts into two (related) tables

[Francisco Olarte]

Rich:

On Wed, May 22, 2019 at 6:07 PM Rich Shepard  wrote:
> On Wed, 22 May 2019, Francisco Olarte wrote:
> > You are not reading what we write to you. Note YOU AND ONLY YOU are the
> > one speaking of PK. We are speaking of "unique identifier" ( that would
> > be, IIRC, "candidate keys", you can peek any as your PK, or even introduce
> > a new synthetic one with a sequence, or a femto second exact timestamp or
> > whatever ).

> The organizations table has org_id (an integer) as PK.
> The people table has person_id (an interger) as PK and org_id as the
> reference to organization.org_id.
> Does this help?

It does. But if, as I assume, you are referring to the FINAL tables
where you have the data inserted, to suggest a good method we would
also need to know HOW are the org_id / person_id generated ( i.e.
sequences, by hand, externally assigned ).

Also, when I speak of "unique identifier" I'm not speaking of the one
if your FINAL tables, I assume you would have at least the *_id field
as PKEY, so nothing else needed, but the one in your SOURCE data set (
it can be anything, like the row number in the original excel ). If
you read the examples I sent you I have "unique identifiers" for
organizations in both of them, in the first one it was the
organization name (clavelitos/bits cuadrados) in the other one it was
"the previously non indented line". Both can be processed with a
little aws/perl  +sql or with a lot of sql ( i.e., the indented one
can be loaded in a big temp table with a lot of text fields, or a
single one, and then split with sql functions, but perl/awk is much
easier ).

> > When you are fluent in SQL you do not try to play with files, you import
> > every column of your data into temporary tables, clean them up, and join (
> > if needed ) them until you have a select that gives you what you want and
> > then insert this. Normally you insert several SELECTS into temporary
> > tables ( specially when you only have thousands of records ) so you can do
> > the clean up in steps.
> Most of my time is spent writing using LaTeX/LyX. Depending on the project's
> needs I'll also use SQL, R, GRASS, and other tools. I'm a generalist, like
> your PCP, not a specialist. But, I also rely on emacs, grep, sed, and awk
> for data munging and am more fluent with these tools than I am with SQL or
> Python.

MMM, apart from angel dust I do not know what PCP could stand for. But
anyway, for simple one liners and data filtering, sed/awk are more
than enough ( I substitute them with perl because it can do the same
in a little more verbose way and I had already learned it when
confronted with awk/sed ) ( I do not find Python particularly
palatable for one-shot text processing, YMMV ). If you are not too
fluent in SQL you normally do the pre-cleaning step in emacs ( marking
lines on it's type, pasting long ones, there is no substitute to
eyeballing for that ), then use a couple scripts to separate data
types (orgs and persons ) in distinct files and clean extraneous
quotes, commas etc(sed, pure shell, awk, any language can do this ).
At this stage you have something like a "clean CSV dump" ( although I
would recommend to use the old format of postgres dumps, newline
terminated, tab delimited, backslash scaped, it's much, much easier to
work with ). Then you import this into a couple of temporary table and
use sql to do what it is good at ( adding a sequential column and
populating it with unique ids, populating foreign key columns by
joining on a collection of fields, and slicing the useful columns to
its final destination).

And, if you hit a snag, you ask, but ask with a good description of
your problem. You have extra knowledge which you are trickling down to
us, which leads to huge frustration. We want to help, but you are not
letting us, probably not on purpose, by posting an incomplete
description of your problem and refusing to answer what we think are
simple and basic questions ( I assume this is due to "impedance
mismatch", not bad faith, otherwise I would just have blacklisted you
several messages ago ). From what I know of your problem so far, A
thousand lines, in csv, two tables, with some way to match one with
the other, my money is it could have been solved in much less time
than this message is taking giving the right info in the first place.


> For me, the quickest and simplest appoach is to add the PKs to each table,
> and the org_id into the people table, when I separate the cleaned text file
> into the columns for each table.

That I do a lot, for small files. Being very fluent in perl, which
excels at text file crunching, I normally go to one-liners as soon as
I hit the couple of screens size.

The problem is you stated a problem, we tried to help you, but it
seemed like you did not want to be. We still do not know zilch about
your problem. I.e., ¿ Are there more than one person per organization
( I assume so, as you are talking of FK in persons to org ). So, given
two persons belonging to the 

Re: Bulk inserts into two (related) tables

[Jeremy Finzel]

On Wed, May 22, 2019 at 11:07 AM Rich Shepard 
wrote:

> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > You are not reading what we write to you. Note YOU AND ONLY YOU are the
> > one speaking of PK. We are speaking of "unique identifier" ( that would
> > be, IIRC, "candidate keys", you can peek any as your PK, or even
> introduce
> > a new synthetic one with a sequence, or a femto second exact timestamp or
> > whatever ).
>
> Francisco,
>
> Let me clarify.
>
> The organizations table has org_id (an integer) as PK.
>
> The people table has person_id (an interger) as PK and org_id as the
> reference to organization.org_id.
>
> Does this help?
>

Francisco hit just the point I was making.  I never said org_name should be
the primary key.  We all acknowledge org_id will the the primary key.  But
the whole question that would solve your issue is what other field or
fields identify an organization in your data file.

You need two unique identifiers:

   1. A "natural" one from your data file that identifies unique
   information about an organization.  This is NOT the primary key on
   organizations table
   2. A "synthetic" one - org_id, generated in the organizations table.

The whole point of the (1) natural unique identifier is this is how you can
*easily* link org_id back to the person record in your data file.

You say we don't need to see your data file to answer the question.  To me,
it's still clear that if you were to share a sample of your data file
(obviously, with fake data), all of this back and forth would quickly end.
This is all way too abstract.

Thanks,
Jeremy

Table partition with primary key in 11.3

[User]

CREATE TABLE public.test1 (
x1 integer NOT NULL,
x2 integer NOT NULL,
CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
) PARTITION BY RANGE (x2);

This query works in 11.1 but fails in 11.3 with messages:

ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "test1" lacks column "x2" which 
is part of the partition key.

SQL state: 0A000

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Francisco Olarte wrote:


Also, when I speak of "unique identifier" I'm not speaking of the one if
your FINAL tables, I assume you would have at least the *_id field as
PKEY, so nothing else needed, but the one in your SOURCE data set (it can
be anything, like the row number in the original excel).


Francisco/Jeremy,

I'm grateful for you patient help. The 'unique identifier' in the source
file has been provided (just now) using nl .
The syntax I used is:

nl -b a -n ln -s , -v 339 source.txt > out.txt

because the organizations table has 338 as the maximum org_id number.

I believe this fulfills the need for a known unique ID in the source file,
and when I parse each row using gawk to create the two files for table input
I can use it in both the organizations table (as the PK) and the people
table (as the FK referring to the organizations table). I can let postgres
assign the unique ID for the new rows in the people table.

Am I still missing something critical?


MMM, apart from angel dust I do not know what PCP could stand for.


Primary Care Physician.

Best regards,

Rich

Re: pg_stat_user_tables.n_tup_ins empty for partitioned table

[Andres Freund]

Hi,

On 2019-05-22 10:08:44 +0200, Luca Ferrari wrote:
> I've got a table named "root", partitioned on a date field into years
> (e.g., "2018") and into months like "y2018m11" using range
> partitioning on PostgreSQL 11.2.
> Tuples are inserted into root with an INSERT...SELECT. I have
> performed an UPDATE due to a new column inserted in the parent table
> y2018.
> 
> Today, checking the statistics of the table, I found that no tuples
> were inserted:
> 
> testdb=> SELECT relname, seq_scan, idx_scan,
>  n_tup_ins, n_tup_del, n_tup_upd, n_tup_hot_upd,
>  n_live_tup, n_dead_tup,
>  last_vacuum, last_autovacuum,
>  last_analyze, last_autoanalyze
>  FROM pg_stat_user_tables;
> 
> -[ RECORD 12 ]---+--
> relname  | y2018m11
> seq_scan | 42172
> idx_scan |
> n_tup_ins| 0
> n_tup_del| 0
> n_tup_upd| 28191462
> n_tup_hot_upd| 86
> n_live_tup   | 14086279
> n_dead_tup   | 0
> last_vacuum  |
> last_autovacuum  | 2019-03-28 17:23:35.909943+01
> last_analyze |
> last_autoanalyze | 2019-03-28 17:25:12.773707+01
> 
> 
> I suspect the fact that n_tup_ins is 0 is due to the fact that I did
> insert the tuples into the parent y2018, but I would like to better
> understand: how can have n_live_tup without having n_tup_ins?
> Moreover, I don't have any stat for the parent table root, neither for
> y2018.
> Some explaination/pointer is appreciated.

That clearly seems wrong. Could you try build a small reproducer?

Greetings,

Andres Freund

Re: Table partition with primary key in 11.3

[Tom Lane]

User  writes:
> CREATE TABLE public.test1 (
>  x1 integer NOT NULL,
>  x2 integer NOT NULL,
>  CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
> ) PARTITION BY RANGE (x2);

> This query works in 11.1 but fails in 11.3 with messages:

> ERROR: insufficient columns in PRIMARY KEY constraint definition
> DETAIL: PRIMARY KEY constraint on table "test1" lacks column "x2" which 
> is part of the partition key.
> SQL state: 0A000

Indeed, that primary key is no good.  It was a bug that 11.1
allowed it, which was fixed here:

Author: Alvaro Herrera 
Branch: master [0ad41cf53] 2019-01-14 19:28:10 -0300
Branch: REL_11_STABLE Release: REL_11_2 [74aa7e046] 2019-01-14 19:25:19 -0300

Fix unique INCLUDE indexes on partitioned tables

We were considering the INCLUDE columns as part of the key, allowing
unicity-violating rows to be inserted in different partitions.

Concurrent development conflict in eb7ed3f30634 and 8224de4f42cc.

Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/20190109065109.ga4...@telsasoft.com

regards, tom lane

Re: Bulk inserts into two (related) tables

[Jeremy Finzel]

On Wed, May 22, 2019 at 12:53 PM Rich Shepard 
wrote:

> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > Also, when I speak of "unique identifier" I'm not speaking of the one if
> > your FINAL tables, I assume you would have at least the *_id field as
> > PKEY, so nothing else needed, but the one in your SOURCE data set (it can
> > be anything, like the row number in the original excel).
>
> Francisco/Jeremy,
>
> I'm grateful for you patient help. The 'unique identifier' in the source
> file has been provided (just now) using nl  >.
> The syntax I used is:
>
> nl -b a -n ln -s , -v 339 source.txt > out.txt
>
> because the organizations table has 338 as the maximum org_id number.
>
> I believe this fulfills the need for a known unique ID in the source file,
> and when I parse each row using gawk to create the two files for table
> input
> I can use it in both the organizations table (as the PK) and the people
> table (as the FK referring to the organizations table). I can let postgres
> assign the unique ID for the new rows in the people table.
>
> Am I still missing something critical?


Are you saying your database already has an organizations table, and this
data file is appending to it with all-brand-new organizations and people?

Say you have 800 people.  Are you honestly saying that there are also 800
organizations?  If so, I guess your solution would work.  However, I
thought that it was the case that multiple people belong to the same
organization.  Hence, the entire problem of mapping we have been discussing.

The data model doesn't make much sense if that's true.

Also, you said previously that some fields from your data file would be
populated in *both tables*.  That is a fundamental violation of DB design.
Why are you duplicating that data in two places?

Thanks,
Jeremy

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Jeremy Finzel wrote:


Are you saying your database already has an organizations table, and this
data file is appending to it with all-brand-new organizations and people?


Jeremy,

The database has both organizations and people tables (among others) which
are already populated. The source file appends rows to both tables.


Say you have 800 people. Are you honestly saying that there are also 800
organizations? If so, I guess your solution would work. However, I thought
that it was the case that multiple people belong to the same organization.
Hence, the entire problem of mapping we have been discussing.


No. But, each organization may have several people, each one at a different
facility. Think of a bank with one headquarters but different mangers in
each of their branches.


Also, you said previously that some fields from your data file would be
populated in *both tables*. That is a fundamental violation of DB design.
Why are you duplicating that data in two places?


Not if the duplicated data field is the primary key in the organizations
table and the referenced foreign key in the people table.

Regards,

Rich

Re: Bulk inserts into two (related) tables

[Adrian Klaver]

On 5/22/19 10:53 AM, Rich Shepard wrote:

On Wed, 22 May 2019, Francisco Olarte wrote:


Also, when I speak of "unique identifier" I'm not speaking of the one if
your FINAL tables, I assume you would have at least the *_id field as
PKEY, so nothing else needed, but the one in your SOURCE data set (it can
be anything, like the row number in the original excel).


Francisco/Jeremy,

I'm grateful for you patient help. The 'unique identifier' in the source
file has been provided (just now) using nl .
The syntax I used is:

nl -b a -n ln -s , -v 339 source.txt > out.txt

because the organizations table has 338 as the maximum org_id number.

I believe this fulfills the need for a known unique ID in the source file,
and when I parse each row using gawk to create the two files for table 
input

I can use it in both the organizations table (as the PK) and the people
table (as the FK referring to the organizations table). I can let postgres
assign the unique ID for the new rows in the people table.

Am I still missing something critical?


A sample of the data you are cleaning up.

I think what people are trying to wrap there head around is how 800 
lines in the file is being split into two subsets: the organization data 
and the people data. In particular how that is being done to preserve 
the relationship between organizations and people? This is before it 
ever gets to the database.






MMM, apart from angel dust I do not know what PCP could stand for.


Primary Care Physician.

Best regards,

Rich







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Bulk inserts into two (related) tables

[Jeremy Finzel]

>
> A sample of the data you are cleaning up.
>
> I think what people are trying to wrap there head around is how 800
> lines in the file is being split into two subsets: the organization data
> and the people data. In particular how that is being done to preserve
> the relationship between organizations and people? This is before it
> ever gets to the database.
>

I agree.  We keep going in circles and until we have a sample of the data,
it's not a good use of time for us to keep guessing at what none of us is
clear about but you - what the data actually looks like.

Thanks,
Jeremy

Re: Bulk inserts into two (related) tables

[Rich Shepard]

On Wed, 22 May 2019, Adrian Klaver wrote:


A sample of the data you are cleaning up.


Adrian, et al.:

I have it working properly now. Both org_id and person_id numbers are
prepended to each row in the appropriate table and they are unique because
each series begins one greater than the max(*_id) in each table.


I think what people are trying to wrap there head around is how 800 lines
in the file is being split into two subsets: the organization data and the
people data. In particular how that is being done to preserve the
relationship between organizations and people? This is before it ever gets
to the database.


After cleaning there are 655 lines rather than 800, but the short answer is
that spliting the data preserves the relationship between organization and
its people:

#!/usr/bin/gawk

# Read input file, write fields to both organizations and people
# input files.

BEGIN { FS=OFS="," }
# for organizations table input:
{ print $1, $2, $4, "," $8, $9, $10, "'US'," $11, ",,," "'Opportunity','');" > 
"z-orgs.sql" }
# for people table input:
{ print $6, $5, "," $1, $3, $4, $5, $7, $8, $9, $10, "'US'," $11, "," $12, "," $13, 
"'true','');" > "z-people.sql" }

You can see that the org_id field ($1) is used in both files, but in
different columns in the two tables.

Regards,

Rich

Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n

[Perumal Raj]

Hi All,

We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration is
Apple-Apple.

But We started seeing lot of process name 'migration' at OS Level in new
server which is triggering Load average most of the time  .

Some sample process from top command,

67 root  RT   0 000 R 34.8  0.0 913:06.38
[migration/16]

   155 root  RT   0 000 S 32.2  0.0 845:46.53
[migration/38]

35 root  RT   0 000 S 27.6  0.0 927:15.27
[migration/8]

11 root  RT   0 000 S 21.4  0.0   1033:45
[migration/2]

   131 root  RT   0 000 S 21.4  0.0 812:00.70
[migration/32]

87 root  RT   0 000 S 20.7  0.0 394:28.32
[migration/21]

Anyone come-across this situation ? Please share your thoughts .

Thanks,
Raju

Re: Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n

[Adrian Klaver]

On 5/22/19 2:18 PM, Perumal Raj wrote:

Hi All,

We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration 
is Apple-Apple.


Other programs running that connect to the db server?
More below.


But We started seeing lot of process name 'migration' at OS Level in new 
server which is triggering Load average most of the time  .


Going to need to determine what 'migration' is referring to?

I would start by hitting 'c' in top to toggle full process name.



Some sample process from top command,

     67 root      RT   0     0    0    0 R 34.8  0.0 913:06.38 
[migration/16]
    155 root      RT   0     0    0    0 S 32.2  0.0 845:46.53 
[migration/38]
     35 root      RT   0     0    0    0 S 27.6  0.0 927:15.27 
[migration/8]
     11 root      RT   0     0    0    0 S 21.4  0.0   1033:45 
[migration/2]
    131 root      RT   0     0    0    0 S 21.4  0.0 812:00.70 
[migration/32]
     87 root      RT   0     0    0    0 S 20.7  0.0 394:28.32 
[migration/21]


Anyone come-across this situation ? Please share your thoughts .

Thanks,
Raju



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n

[Thomas Munro]

On Thu, May 23, 2019 at 9:18 AM Perumal Raj  wrote:
> We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
> Both Source and Target versions of OS/DB are same . Also Configuration is 
> Apple-Apple.
>
> But We started seeing lot of process name 'migration' at OS Level in new 
> server which is triggering Load average most of the time  .
>
> Some sample process from top command,
>
> 67 root  RT   0 000 R 34.8  0.0 913:06.38 [migration/16]
>155 root  RT   0 000 S 32.2  0.0 845:46.53 [migration/38]
> 35 root  RT   0 000 S 27.6  0.0 927:15.27 [migration/8]
> 11 root  RT   0 000 S 21.4  0.0   1033:45 [migration/2]
>131 root  RT   0 000 S 21.4  0.0 812:00.70 [migration/32]
> 87 root  RT   0 000 S 20.7  0.0 394:28.32 [migration/21]
>
> Anyone come-across this situation ? Please share your thoughts .

I don't know anything about this, but I found a claim (without much
real explanation) that Linux < 3.6.11 had some kind of problem in this
area, could be relevant:

https://serverfault.com/questions/674685/kernel-processes-periodically-eating-cpu-during-high-load
https://bugs.gentoo.org/394487
https://bugzilla.kernel.org/show_bug.cgi?id=47341

-- 
Thomas Munro
https://enterprisedb.com

Trigger bug ?

[PegoraroF10]

We created a usual trigger which seemed to be not firing or not working
properly. Then we put some raise notices on it and saw that it was returning
Null values. But why, it´s after insert and is a primary key, cannot be
null.

create function MyFunction() returns trigger as $$
begin
  raise '%', new.MyPK;
end$$ language plpgsql;

create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();

We forgot FOR EACH ROW/STATEMENT when created our trigger.

On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why did
it accepted our command to create that trigger ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Trigger bug ?

[Adrian Klaver]

On 5/22/19 2:33 PM, PegoraroF10 wrote:

We created a usual trigger which seemed to be not firing or not working
properly. Then we put some raise notices on it and saw that it was returning
Null values. But why, it´s after insert and is a primary key, cannot be
null.


https://www.postgresql.org/docs/11/plpgsql-trigger.html
"The return value of a row-level trigger fired AFTER or a 
statement-level trigger fired BEFORE or AFTER is always ignored; it 
might as well be null. However, any of these types of triggers might 
still abort the entire operation by raising an error."


More below.



create function MyFunction() returns trigger as $$
begin
   raise '%', new.MyPK;
end$$ language plpgsql;

create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();

We forgot FOR EACH ROW/STATEMENT when created our trigger.

On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why did
it accepted our command to create that trigger ?


https://www.postgresql.org/docs/11/sql-createtrigger.html
"FOR EACH ROW
FOR EACH STATEMENT

This specifies whether the trigger function should be fired once 
for every row affected by the trigger event, or just once per SQL 
statement. If neither is specified, FOR EACH STATEMENT is the default. 
Constraint triggers can only be specified FOR EACH ROW.

"




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Trigger bug ?

[David G. Johnston]

On Wed, May 22, 2019 at 2:33 PM PegoraroF10  wrote:

> We forgot FOR EACH ROW/STATEMENT when created our trigger.
>
> On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why
> did
> it accepted our command to create that trigger ?
>

You only partially absorbed the syntax doc for that...in full it reads:

...
[ FOR [ EACH ] { ROW | STATEMENT } ]

You omitted the whole thing which is allowed.

David J.

Re: Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n

[Perumal Raj]

Thanks for the responses,

Adrian,

top -c not showing much , Process name itself [ Migration/n]

Thomas,

Thanks for hitting same page, yes i came through it , But was wondering any
one faced same issue in our community.

Regards,
Raju

On Wed, May 22, 2019 at 2:28 PM Thomas Munro  wrote:

> On Thu, May 23, 2019 at 9:18 AM Perumal Raj  wrote:
> > We have recently migrated postgres DB to out of of server ( Centos 6.9 )
> .
> > Both Source and Target versions of OS/DB are same . Also Configuration
> is Apple-Apple.
> >
> > But We started seeing lot of process name 'migration' at OS Level in new
> server which is triggering Load average most of the time  .
> >
> > Some sample process from top command,
> >
> > 67 root  RT   0 000 R 34.8  0.0 913:06.38
> [migration/16]
> >155 root  RT   0 000 S 32.2  0.0 845:46.53
> [migration/38]
> > 35 root  RT   0 000 S 27.6  0.0 927:15.27
> [migration/8]
> > 11 root  RT   0 000 S 21.4  0.0   1033:45
> [migration/2]
> >131 root  RT   0 000 S 21.4  0.0 812:00.70
> [migration/32]
> > 87 root  RT   0 000 S 20.7  0.0 394:28.32
> [migration/21]
> >
> > Anyone come-across this situation ? Please share your thoughts .
>
> I don't know anything about this, but I found a claim (without much
> real explanation) that Linux < 3.6.11 had some kind of problem in this
> area, could be relevant:
>
>
> https://serverfault.com/questions/674685/kernel-processes-periodically-eating-cpu-during-high-load
> https://bugs.gentoo.org/394487
> https://bugzilla.kernel.org/show_bug.cgi?id=47341
>
> --
> Thomas Munro
> https://enterprisedb.com
>

Re: Trigger bug ?

[PegoraroF10]

sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result.
Try to create exactly what I sent you and you´ll see null values on that pk. 
And that occurs just because that trigger was created without EACH
ROW/STATEMENT. 
So, my question is, PG should return an error and not create that trigger
because ROW/STATEMENT was not specified, correct ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Trigger bug ?

[David G. Johnston]

On Wed, May 22, 2019 at 3:21 PM PegoraroF10  wrote:

> Try to create exactly what I sent you and you´ll see null values on that
> pk.
>

You should probably submit self-contained examples if you need the user to
do this.


> And that occurs just because that trigger was created without EACH
> ROW/STATEMENT.



> So, my question is, PG should return an error and not create that trigger

because ROW/STATEMENT was not specified, correct ?
>

PostgreSQL doesn't inspect the called function to see if it is written
correctly for a given invocation of CREATE TRIGGER.  So, no, given that
your command was syntactically valid PostgreSQL doesn't have any reason to
return an error.

David J.

Re: Trigger bug ?

[PegoraroF10]

I´m not saying it should inspect function code, but I think it should deny
when I try to create a trigger missing a needed argument.

When I do ...
create table MyTable(integer); 
gives me an "syntax error at end of input" because I forgot field name.

why when I do ...
create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();
It does not gives me something similar ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Trigger bug ?

[Adrian Klaver]

On 5/22/19 3:21 PM, PegoraroF10 wrote:

sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result > Try to create exactly what I sent you and you´ll see null values on 

that pk.

Yes because FOR EACH STATEMENT may deal with multiple rows, so it is 
undefined what NEW.pk is referring to.


More below.


And that occurs just because that trigger was created without EACH
ROW/STATEMENT.
So, my question is, PG should return an error and not create that trigger
because ROW/STATEMENT was not specified, correct ?


It was implicitly specified. Per the docs it will be FOR EACH STATEMENT 
by default in the absence of user input.


If you just need the new PK why not?:

INSERT INTO some_table ... RETURNING pk;





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Trigger bug ?

[David G. Johnston]

On Wed, May 22, 2019 at 3:41 PM PegoraroF10  wrote:

> I´m not saying it should inspect function code, but I think it should deny
> when I try to create a trigger missing a needed argument.
>

Guessing you missed my earlier response...


> When I do ...
> create table MyTable(integer);
> gives me an "syntax error at end of input" because I forgot field name.
>
> why when I do ...
> create trigger MyTrigger after insert on MyTable execute procedure
> MyFunction();
> It does not gives me something similar ?
>

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event
[ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY
DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [
... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )

The part about { ROW | STATEMENT } is within an optional clause.

David J.

Re: Trigger bug ?

[PegoraroF10]

ok, you won. Sorry, I didn´t read that "If neither is specified, FOR EACH
STATEMENT is the default"



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Query reg. postgresql 9.6 migration from ubuntu 16.04 to 18.04

[a venkatesh]

Hi,

I'm working on migrating postgresql 9.6 database from ubuntu 16.04 to
18.04. Please let me know if there are any document references and best
practices that can be followed.

Thanks,
venkatesh.

Re: Query reg. postgresql 9.6 migration from ubuntu 16.04 to 18.04

[Adrian Klaver]

On 5/22/19 5:03 PM, a venkatesh wrote:

Hi,

I'm working on migrating postgresql 9.6 database from ubuntu 16.04 to 
18.04. Please let me know if there are any document references and best 
practices that can be followed.


Advice is going to depend on:

1) Size of Postgres cluster.

2) Method of migration:
Dump/restore
Replication
Etc

3) How are you installing Postgres:
Ubuntu repos
Postgres community repos

4) Location of one machine relative to other

5) Time frame to do migration.



Thanks,
venkatesh.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n

[Ron]

On 5/22/19 4:18 PM, Perumal Raj wrote:

Hi All,

We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration is 
Apple-Apple.


But We started seeing lot of process name 'migration' at OS Level in new 
server which is triggering Load average most of the time  .


Some sample process from top command,

    67 root      RT   0     0    0    0 R 34.8  0.0 913:06.38 [migration/16]
   155 root      RT   0     0    0    0 S 32.2  0.0 845:46.53 [migration/38]
    35 root      RT   0     0    0    0 S 27.6  0.0 927:15.27 [migration/8]
    11 root      RT   0     0    0    0 S 21.4  0.0 1033:45 [migration/2]
   131 root      RT   0     0    0    0 S 21.4  0.0 812:00.70 [migration/32]
    87 root      RT   0     0    0    0 S 20.7  0.0 394:28.32 [migration/21]

Anyone come-across this situation ? Please share your thoughts .


I googled "linux *root* migration" and found this:
https://serverfault.com/questions/674685/kernel-processes-periodically-eating-cpu-during-high-load

It has some pretty good answers.

--
Angular momentum makes the world go 'round.

Re: distinguish update from insert (on conflict)

[Laurenz Albe]

Justin Pryzby wrote:
> Is it still impossible to distinguish whether a row was inserted vs updated ?
> 
> The latest I can see is here:
> https://wiki.postgresql.org/wiki/UPSERT#RETURNING_behavior
> 
> ..but I'm hopeful that the 4 year old wiki page is out of date.

Maybe this answer can help you:

https://stackoverflow.com/a/39204667/6464308

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Table inheritance over schema boundaries possible

[Thiemo Kellner]

Hi all

I am wondering if table inheritance is possible over the boundaries of  
schemata and different owners.


I have database act with schemata il and scd. When I issue as IL

create table IL.INHERITANCE_TEST() inherits (SCD.TEMPL_BK);

I get

[Code: 0, SQL State: 42501]  ERROR: permission denied for schema scd

Even though I granted all privileges to IL.

What am I missing or is it just not possible what I want to do?

Kind regards

Thiemo

--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B


smime.p7s
Description: S/MIME Signature