Unused indexes
For indexes that existed before the cluster was last started, and for which REINDEX hasn't been run on them since the cluster was last started... is it valid to say that an index has not been used since the cluster was started if these three pg_stat_all_indexes fields all have a value of 0? idx_scan idx_tup_read idx_tup_fetch If it matters, the version is 9.6.6. Thanks -- Angular momentum makes the world go 'round.
Transition Tables doesn´t have OID
I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace function AuditTable() returns trigger language plpgsql as $$ if (TG_OP = 'UPDATE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old, values_new) select TG_TABLE_NAME, Transition_new.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb, row_to_json(Transition_new.*)::jsonb from Transition_new inner join Transition_old on Transition_new.OID = Transition_old.OID; elsif (TG_OP = 'DELETE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old) select TG_TABLE_NAME, Transition_old.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb from Transition_old; end if; [42703] ERROR: column transition_new.oid does not exist Where: função PL/pgSQL audittable() linha 14 em comando SQL I would like to user OID value because we change our primary keys, sometimes, OID doesn´t. So, there is a way to get OID on transition tables ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Transition Tables doesn´t have OID
On 12/01/2018 06:22 AM, PegoraroF10 wrote: I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace function AuditTable() returns trigger language plpgsql as $$ if (TG_OP = 'UPDATE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old, values_new) select TG_TABLE_NAME, Transition_new.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb, row_to_json(Transition_new.*)::jsonb from Transition_new inner join Transition_old on Transition_new.OID = Transition_old.OID; elsif (TG_OP = 'DELETE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old) select TG_TABLE_NAME, Transition_old.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb from Transition_old; end if; [42703] ERROR: column transition_new.oid does not exist Where: função PL/pgSQL audittable() linha 14 em comando SQL I would like to user OID value because we change our primary keys, sometimes, OID doesn´t. So, there is a way to get OID on transition tables ? Did you create MyTable WITH OIDS (or set default_with_oids on in postgresql.conf)? https://www.postgresql.org/docs/9.6/datatype-oid.html Also, https://www.postgresql.org/docs/9.6/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS "The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table." More importantly, https://wiki.postgresql.org/wiki/FAQ#What_is_an_OID.3F "OIDs are sequentially assigned 4-byte integers. Initially they are unique across the entire installation. However, the OID counter wraps around at 4 billion, and after that OIDs may be duplicated. It is possible to prevent duplication of OIDs within a single table by creating a unique index on the OID column (but note that the WITH OIDS clause doesn't by itself create such an index). The system checks the index to see if a newly generated OID is already present, and if so generates a new OID and repeats. This works well so long as no OID-containing table has more than a small fraction of 4 billion rows." -- Angular momentum makes the world go 'round.
Re: Unused indexes
I think your assumption is correct, as long as the statistics collector is working correctly (I've never seen this not being the case), and the setting "track_counts" is set to on. Am Sa., 1. Dez. 2018 um 05:24 Uhr schrieb Ron : > For indexes that existed before the cluster was last started, and for > which > REINDEX hasn't been run on them since the cluster was last started... is > it > valid to say that an index has not been used since the cluster was started > if these three pg_stat_all_indexes fields all have a value of 0? > > idx_scan > idx_tup_read > idx_tup_fetch > > If it matters, the version is 9.6.6. > > Thanks > > -- > Angular momentum makes the world go 'round. > > -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: Unused indexes
El sáb., 1 dic. 2018 a las 8:24, Ron () escribió: > > For indexes that existed before the cluster was last started, and for which > REINDEX hasn't been run on them since the cluster was last started... is it > valid to say that an index has not been used since the cluster was started > if these three pg_stat_all_indexes fields all have a value of 0? > > idx_scan Just this one, and that the index doesn't force a constraint (it's not a unique index). One other thing to keep in mind is that, if you have hot_standby replicas, the index might be used there, and the primary doesn't have information of index_scans on other nodes of the cluster. Regards, -- Martín Marqués It’s not that I have something to hide, it’s that I have nothing I want you to see
Re: Transition Tables doesn´t have OID
yes, mytable has OID column select oid, ID, Name from MyTable limit 3 oid id name 279515 1104Carol 279516 1106Dalva 279517 11008354Melissa -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Transition Tables doesn´t have OID
On 12/1/18 4:22 AM, PegoraroF10 wrote: I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace function AuditTable() returns trigger language plpgsql as $$ if (TG_OP = 'UPDATE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old, values_new) select TG_TABLE_NAME, Transition_new.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb, row_to_json(Transition_new.*)::jsonb from Transition_new inner join Transition_old on Transition_new.OID = Transition_old.OID; elsif (TG_OP = 'DELETE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old) select TG_TABLE_NAME, Transition_old.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb from Transition_old; end if; [42703] ERROR: column transition_new.oid does not exist Where: função PL/pgSQL audittable() linha 14 em comando SQL I would like to user OID value because we change our primary keys, sometimes, OID doesn´t. So, there is a way to get OID on transition tables ? You will want to get away from using OID's as they are going away: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=578b229718e8f15fa779e20f086c4b6bb3776106 -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Process for populating tables in new database
I have a new database with five tables, and text files with data to populate
the tables using the insert command.
1. Each table has a sequential primary key. Should I manually add keys to
each insert row or will postgres generate it automatically? Example, for the
companies table:
org_id| integer | | not null |
nextval('companies_org_id_seq'::regclass)
org_name | character varying(64) | | not null |
2. Should I use sepatate files as input to each table or can I combine them
in a single file? For example, adding a company name and contact name for
someone in that company using input commands for both tables in a single
file? I will need to provide the primary key to the company table as the
foreign key in contact table and have not before pupulated multiple related
tables in a new database from .sql files.
TIA,
Rich
Re: Transition Tables doesn´t have OID
> "PegoraroF10" == PegoraroF10 writes: PegoraroF10> I would like to user OID value because we change our PegoraroF10> primary keys, sometimes, OID doesn´t. "oid" as a special system column and the WITH OIDS option when creating tables are being removed in pg12 (having been deprecated for something like 12 years now), you might want to start thinking about alternatives. PegoraroF10> So, there is a way to get OID on transition tables ? Well, arguably it is an oversight in the implementation of transition tables that they were not added to the various places in the parser that treat "oid" as a system column name. However, I not sure that you'll get any agreement to fix that in light of the demise of "oid" as mentioned above. (Not least because backpatching it could be dangerous in that it could break queries that now work, by making "oid" an ambiguous column reference.) I tried various workarounds, but they were defeated by the fact that evaluation of a whole-row Var does not copy the oid value (if any). (I'm not sure if this ever really worked, so calling it a bug may be a bit of a stretch.) -- Andrew (irc:RhodiumToad)
Re: Process for populating tables in new database
On 12/1/18 6:09 AM, Rich Shepard wrote:
I have a new database with five tables, and text files with data to
populate
the tables using the insert command.
1. Each table has a sequential primary key. Should I manually add keys to
each insert row or will postgres generate it automatically? Example, for
the
companies table:
If you don't supply the key it will be generated as the default for the
PK column is a sequence.
org_id | integer | | not null |
nextval('companies_org_id_seq'::regclass)
org_name | character varying(64) | | not null |
2. Should I use sepatate files as input to each table or can I combine them
in a single file? For example, adding a company name and contact name for
someone in that company using input commands for both tables in a single
file? I will need to provide the primary key to the company table as the
foreign key in contact table and have not before pupulated multiple related
tables in a new database from .sql files.
So are the tables you are INSERTing into currently unpopulated?
In any case you will need to do this in sequence, where you populate the
company table and then the contact table. The question is whether you
want to pre-assign the company id's in the company data and the
company_id_fk in the contacts data or not. If not then you will need to
grab the company id's after populating the company table and match those
to the contacts data before inserting it.
TIA,
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Process for populating tables in new database
On 12/1/18 6:09 AM, Rich Shepard wrote:
I have a new database with five tables, and text files with data to
populate
the tables using the insert command.
1. Each table has a sequential primary key. Should I manually add keys to
each insert row or will postgres generate it automatically? Example, for
the
companies table:
org_id | integer | | not null |
nextval('companies_org_id_seq'::regclass)
org_name | character varying(64) | | not null |
2. Should I use sepatate files as input to each table or can I combine them
in a single file? For example, adding a company name and contact name for
someone in that company using input commands for both tables in a single
file? I will need to provide the primary key to the company table as the
foreign key in contact table and have not before pupulated multiple related
tables in a new database from .sql files.
Forgot to add that if you pre-assign the keys to the serial field you
will need to advance the sequence to a value past the last key value to
avoid a duplicate key error when you let the sequence assign numbers.
TIA,
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Process for populating tables in new database [RESOLVED]
On Sat, 1 Dec 2018, Adrian Klaver wrote: If you don't supply the key it will be generated as the default for the PK column is a sequence. Thanks, Adrian. I thought this to be the case and did not find confirmation in the manual (perhaps I just missed seeing it.) So are the tables you are INSERTing into currently unpopulated? Yes. In any case you will need to do this in sequence, where you populate the company table and then the contact table. The question is whether you want to pre-assign the company id's in the company data and the company_id_fk in the contacts data or not. If not then you will need to grab the company id's after populating the company table and match those to the contacts data before inserting it. This clears up everything. I'll let pg assign company id's then use them as you write to relate the other tables to the proper company/contact. Much appreciated, Rich
Re: Process for populating tables in new database
On Sat, 1 Dec 2018, Adrian Klaver wrote: Forgot to add that if you pre-assign the keys to the serial field you will need to advance the sequence to a value past the last key value to avoid a duplicate key error when you let the sequence assign numbers. Adrian, Got it, thanks. Carpe weekend, Rich
Re: Transition Tables doesn´t have OID
ok then, help me to find alternatives to it. As I sad, sometimes whe change our PK, so using OID would be a smart way to have a join between old and new transition tables and we would like to use transition tables because each statement is a lot faster than each row for auditing purposes. So, whats the alternative ? One trigger for each row just for changing PK values (will occur few times) and another trigger for each statement to do the logging ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Transition Tables doesn´t have OID
On 1/12/18 6:51 μ.μ., PegoraroF10 wrote: ok then, help me to find alternatives to it. As I sad, sometimes whe change our PK, so using OID would be a smart way to have a join between old and new transition tables and we would like to use transition tables because each statement is a lot faster than each row for auditing purposes. Unique key with a sequence, which you'll have to leave alone. So, whats the alternative ? One trigger for each row just for changing PK values (will occur few times) and another trigger for each statement to do the logging ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Transition Tables doesn´t have OID
On 12/1/18 8:51 AM, PegoraroF10 wrote: ok then, help me to find alternatives to it. As I sad, sometimes whe change our PK, so using OID would be a smart way to have a join between old and new transition tables and we would like to use transition tables because each statement is a lot faster than each row for auditing purposes. So, whats the alternative ? One trigger for each row just for changing PK values (will occur few times) and another trigger for each statement to do the logging ? If you don't want to change the structure of your tables that would be seem to be the way to go. It will require some thought to make sure the 'for each row' and 'for each statement' don't interfere with each other. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: Transition Tables doesn´t have OID
On Sun, Dec 2, 2018 at 7:38 AM Adrian Klaver wrote: > > On 12/1/18 8:51 AM, PegoraroF10 wrote: > > ok > > then, help me to find alternatives to it. > > As I sad, sometimes whe change our PK, so using OID would be a smart way to > > have a join between old and new transition tables and we would like to use > > transition tables because each statement is a lot faster than each row for > > auditing purposes. > > > > So, whats the alternative ? One trigger for each row just for changing PK > > values (will occur few times) and another trigger for each statement to do > > the logging ? > > If you don't want to change the structure of your tables that would be > seem to be the way to go. It will require some thought to make sure the > 'for each row' and 'for each statement' don't interfere with each other. I also contemplated this question when hacking on transition tables. We know that the new and old tuplestores are ordered in the same way (as an implementation detail), but there is no way to take advantage of that knowledge in SQL, where relations are unordered sets. There is a syntax WITH ORDINALITY that SQL uses to deal with the fact that set-returning functions' results might really be be ordered, and you might not want to lose that information. Suppose we allowed WITH ORDINALITY for transition tables, so that the 'capture' order of rows could be exposed, and we promised that old and new ORDINALTITY numbers will line up, and then we made the ORDINALITY column a pathkey of the scan. Now you could join old and new tables by the ORDINALITY column, and get a merge join without any sorting. That's... pretty weird though, and the syntax would be outside the SQL spec, and the semantics might be questionable. -- Thomas Munro http://www.enterprisedb.com
