Re: [GENERAL] Performance on DISABLE TRIGGER
David Steele wrote > > ALTER TABLE requires an exclusive lock - my guess is that another > process has a lock on the table. It could even be a select. > > pg_locks is your friend in this case: > http://www.postgresql.org/docs/9.4/static/view-pg-locks.html Hi David I'm a bit confused on how to interpret the result of the pg_locks view. After running the following (as per linked page). SELECT pl.* , psa.query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid; I get a result of 2 locks on the "ALTER TABLE ..." statement: Expanded display is on. -[ RECORD 1 ]--+- locktype | virtualxid database | relation | page | tuple | virtualxid | 5/57182 transactionid | classid| objid | objsubid | virtualtransaction | 5/57182 pid| 6128 mode | ExclusiveLock granted| t fastpath | t query | ALTER TABLE tab DISABLE TRIGGER trig; -[ RECORD 2 ]--+ locktype | relation database | 16393 relation | 22595 page | tuple | virtualxid | transactionid | classid| objid | objsubid | virtualtransaction | 5/57182 pid| 6128 mode | AccessExclusiveLock granted| f fastpath | f query | ALTER TABLE tab DISABLE TRIGGER trig; Something else I noticed while checking out the Server Status window in the pgAdmin tool: The stats_activity query run there displays a "blocked by" column , which I'm assuming is retrieved using data from pg_locks . I assume I'm seeing the pid of the process which is causing the block. THe process however, is a query generated by the pgADmin tool itself: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype FROM pg_type WHERE oid=1700 May this be the case of pg_catalog data being in need of maintenance ? Regards -- View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727p5840221.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance on DISABLE TRIGGER (resend)
Greg Sabino Mullane wrote > Disabling triggers requires a heavy lock. A better way is to use > the session_replication_role feature. See: > > http://blog.endpoint.com/2015/01/postgres-sessionreplication-role.html This is a very effective solution to my problem. Thanks for the tip, Greg. -- View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727p5840247.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using Function returning setof record in JOIN
Hi Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? e.g. I have a function returning a SETOF records (using OUT parameters) with the following output: testdb=# SELECT * FROM myfunc( 'AAA1' ); -- returns calculcated values for all orders for account 'AAA1' _acc | _order | _date | _calc_amount ++-+-- AAA1 | ORDER_1| 2010-12-13 | 1000.00 AAA1 | ORDER_2| 2010-12-13 |80.00 AAA1 | ORDER_5| 2010-12-13 |10.00 (the example is oversimplified - _calc_amount is one of many calculated values returned by the funtion) I also have a VIEW returning the following: testdb=# SELECT _accno, _client, _deladdress, _orderno FROM orders; _accno | _client| _deladdress | _orderno | more order related data ++---++- AAA1 | JOHN | SMITH STR | ORDER_1| AAA1 | JOHN | MAIN STR | ORDER_2| AAA1 | JOHN | PARK RD | ORDER_5| CCC1 | CHARLIE| 2ND STR | ORDER_3| BBB1 | BENN | 5TH AVE | ORDER_4| I want to do a JOIN resulting in: _acc | _order | _date | _amount | _client| _deladdress |more order related data ++-+---++---+-- AAA1 | ORDER_1| 2010-12-13 | 1000.00 | JOHN | SMITH STR | AAA1 | ORDER_2| 2010-12-13 |80.00 | JOHN | MAIN STR | AAA1 | ORDER_5| 2010-12-13 |10.00 | JOHN | PARK RD | Hope this is possible. Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance on DISABLE TRIGGER
Hi all I'm doing some maintenance - which is done quite often, never had this problem before - which requires me to disable triggers, run some updates and then re-enable the triggers. Where the whole process normally take 30 sec , it took much longer today and I cancelled after 5 minutes. After running the statements individually to pinpoint the issue , I identified that the problem is on the first line. >> ALTER TABLE tab DISABLE TRIGGER trig; I have not been able to run this successfully -- after 10 minutes the connection ( to remote DB ) got severed . Any ideas on where I can start to look for the cause of the problem ? Thanks -- View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CREATE TABLE & composite type
HiReferencing https://www.postgresql.org/docs/9.6/static/rowtypes.htmlTaking a chance here Is there a short-hand way in which I can create a table with the same structure as a user defined composite type ? E.g.CREATE TYPE inventory_item AS (nametext,supplier_id integer, price numeric);CREATE TABLE inventory_item_table ( like type inventory_item );We're using composite types rather extensively as the return structure of functions:CREATE FUNCTION some_func() RETURNS SETOF inventory_item ; Of course I can:CREATE TABLE inventory_item_table AS ( SELECT some_func( ) );But , in some cases , where the function returns a large number of records I want to create the table beforehand in order to add indexes:CREATE TABLE inventory_item_table ( like type inventory_item );CREATE INDEX idx ON inventory_item_table ( id );INSERT INTO inventory_item_table SELECT some_func();Will appreciate any input.Regardsgmbouwer -- View this message in context: http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] CREATE TABLE & composite type
Adrian Klaver-4 wrote
>
> CREATE TABLE inventory_item_table AS ( SELECT some_func( ) limit 0);
Thanks Adrian
My concern is that this will have to execute all code in the function only
to return a empty dataset - his may be ineffective.
A possible workaround will be to have a dedicated param which will determine
whether or not to exit at the start of the function.
I also just realised that a possible workaround will be to create tables
instead of composite types ("Whenever you create a table, a composite type
is also automatically created").
I.e. instead of doing:
CREATE TYPE inventory_item AS ( name text, supplier_id integer, price
numeric );
create an actual table (which will never be used):
CREATE TABLE inventory_item AS ( name text, supplier_id integer, price
numeric );
However, having unused tables in the schema kind of goes against the
grain...
Regards
--
View this message in context:
http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032p5969045.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CREATE TABLE & composite type
Merlin Moncure-2 wrote > postgres=# create type foo as (a int, b int); > CREATE TYPE > postgres=# create table bar of foo; > CREATE TABLE > Time: 0.973 ms Exactly what I needed, Thanks a lot, Merlin -- View this message in context: http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032p5969058.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?
Hi For DDL purposes we make significant use of pg_catalog tables/views. Were investigating performance issues in a typical function: CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as $$ SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname=$1 and tablename=$2; $$ language sql When change the params of above function to VARCHAR (instead of TEXT), performance improved dramatically. We then changed params to NAME ( as per pg_tables column type ) , but the performance stayed more or less the same. Can somebody explain this to me ? Is there a better way in which to handle these ? (This will be implemented on most object in the catalog e.g. columns, sequences, functions, etc ) Regards gmb -- View this message in context: http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?
Thanks for taking the time, Tom. Tom Lane-2 wrote > After that, the planner has to implement the query, and the problem > is that the available indexes are on "schemaname" not "schemaname::text", > and they can only use the name = name operator anyway. Did some digging earlier, and found exactly what you refer to here: seq scan when using 'text' and index scan on 'name'. I was not aware that an "incorrect" typecast can have that effect on how the planner choose to use indexes or not use them ( may have to go back and review a lot of other poor performing queries as well ). Tom Lane-2 wrote > Personally I'd have left the function parameters as text and inserted > explicit coercions: Just out of curiosity , is there a reason why this will be you preference ? I ran some benchmarks using *function tableexists( s name, t name )* and the performance turned out pretty well. I guess, from a "readability" point of view it may be unclear to uninformed people what a 'name' type actually is ( it was unknown to me until this morning ), so that may be reason enough to stick with "known" types like TEXT. Thanks, appreciate this. Regards gmb -- View this message in context: http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592p5978619.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?
Thanks for this , Tom -- View this message in context: http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592p5978654.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible encoding issue (win7)
Hi Using a Delphi app with ZEOS components to connect to server (on localhost) and get this message: SQL Error: invalid encoding name in PGCLIENTENCODING: WIN1252 (Running Windows 7 (64x), postgres 8.3.7) Initially I installed with server and client encoding as WIN1252. But since I got the error I tried an re-installing with locale = C, encoding = LATIN1 (server and client). But this did not solve the problem. >From the little info I found on the web, I also tried "SET CLIENT_ENCODING TO 'LATIN1'" but also had no effect. Read a couple of times about *environment variable* PGCLIENTENCODING, but could find no such variable. Heard about colleagues having some problems installing on Windows7. Could this be one of them? Any help would be appreciated. gmbouwer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible encoding issue (win7)
Thank for the quick response Tom > If the message is spelled exactly that way then it's not coming from > anything in the standard Postgres distribution --- we might say > "invalid encoding name "something"" but there's noplace that would > put a reference to PGCLIENTENCODING in the middle. I infer that the > complaint is coming from something in Delphi or ZEOS. You might have > better luck asking in support forums for those products. Will do > FWIW, we added support for WIN1252 encoding in Postgres 8.1. One > possible theory is that this is coming from client-side code that > thinks it knows all the encodings supported by Postgres but hasn't > been updated in a long time. I suspect this is the case - the version of Zeos may be outdated. > > Initially I installed with server and client encoding as WIN1252. > > I'm thinking that this is probably driven by the locale environment on > the client side --- something is trying to set the client_encoding to > match that, and failing for some reason. So changing server encoding > wouldn't help. Is it just coincidence that the message displays WIN1252 - the encoding of my first time install? (BTW this may have been an 8.4 installation, soon after I unintalled and installed 8.3.7). I was wondering (hoping) that there may be some reference (config/ registry) to WIN1252 as enocding which I can locate and remove. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible encoding issue (win7)
Seems all that was necessary was a restart of the server :( ...wish I hadn't spend the whole day on this -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
