[GENERAL] Retrieving the role in a logical replication plugin
I'm working on a 9.4 logical replication plugin, mostly for my own edification, and have run into a limit of my knowledge: How can I retrieve the role (either oid or textual name) associated with the operations my callbacks are receiving? Apologies if it is staring me in the face and I've missed it... -- -- Christophe Pettus x...@thebuild.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] Postgres seems to use indexes in the wrong order
Take a look at this explain http://explain.depesz.com/s/TTRN The final number of records is very small but PG is starting out with a massive number of records and then filtering most of them out. I don't want to really force pg to always use the same index because in some cases this strategy would win but I am wondering if there is anything I need to do in order to get the planner to make better decisions. I already did an analyze on the table.
Re: [GENERAL] pg_upgrade could not connect to server
On Thu, Jan 22, 2015 at 07:13:40PM -0800, Igal @ getRailo.org wrote: > ok, I resolved this by giving Full Permissions to the data directory. > > I don't understand why this problem occurred in the first place. probably > something in the process that creates the Data directory. > > thank you all for your help. That URL I just emailed you explains the cause. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade could not connect to server
On Thu, Jan 22, 2015 at 05:46:08PM -0800, Igal @ getRailo.org wrote: > so when I try to run pg_ctl start I get this now: > > c:\Program Files\PostgreSQL\9.3\bin>pg_ctl --pgdata=E:\PGSQLData start > server starting > > c:\Program Files\PostgreSQL\9.3\bin>2015-01-22 17:25:22 PST PANIC: could not > open control file "global/pg_control": Permission denied > > so looks like that is the source of the problem. > > any ideas? So then it is now this bug report, which I already mentioned: http://www.postgresql.org/message-id/flat/CAEB4t-OHNE95=n5u4yssykwipqswequtbsjkayj63_1vzkz...@mail.gmail.com#CAEB4t-OHNE95=n5u4yssykwipqswequtbsjkayj63_1vzkz...@mail.gmail.com -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade could not connect to server
ok, I resolved this by giving Full Permissions to the data directory. I don't understand why this problem occurred in the first place. probably something in the process that creates the Data directory. thank you all for your help. Igal On 1/22/2015 5:46 PM, Igal @ getRailo.org wrote: > so when I try to run pg_ctl start I get this now: > > c:\Program Files\PostgreSQL\9.3\bin>pg_ctl --pgdata=E:\PGSQLData start > server starting > > c:\Program Files\PostgreSQL\9.3\bin>2015-01-22 17:25:22 PST *PANIC: > could not open control file "global/pg_control": Permission denied* > > so looks like that is the source of the problem. > > any ideas? > > I'm also not sure why this mailing list doesn't use the mailing list's > address for Reply-To. weird... > > > On 1/22/2015 11:30 AM, Igal @ getRailo.org wrote: >> same result :( >> >> C:\Windows\system32>"C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w >> -l "c:\pg_upgrade_ctl.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> waiting for server to startAccess is denied. >> stopped waiting >> pg_ctl: could not start server >> Examine the log output. >> >> can't find any log output either. I expected it to be at >> c:\pg_upgrade_ctl.log given the command above. >> >> >> On 1/22/2015 11:14 AM, Jimmy Jack wrote: >>> runas administrator >>> >>> https://msdn.microsoft.com/en-us/library/bb385791.aspx >>> >>> "The new security model does not grant administrative privileges at >>> all times. Even administrators run under standard privileges when >>> they perform non-administrative tasks that do not require elevated >>> privileges…" >>> >>> >>> >>> On Thu, Jan 22, 2015 at 10:55 AM, Igal @ getRailo.org >>> mailto:i...@getrailo.org>> wrote: >>> >>> Jimmy, >>> >>> On 1/22/2015 10:53 AM, Jimmy Jack wrote: >>> > Did you try to use runas windows command? Should not make any >>> > difference based on your comment how you run it. >>> > >>> > https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx >>> > >>> runas what? there is no "postgres" user on my system. the Windows >>> service runs as Network Service >>> >>> thanks, >>> >>> -- >>> Igal Sapir >>> Railo Core Developer >>> http://getRailo.org/ >>> >>> >> >> -- >> Igal Sapir >> Railo Core Developer >> http://getRailo.org/ > > -- > Igal Sapir > Railo Core Developer > http://getRailo.org/ -- Igal Sapir Railo Core Developer http://getRailo.org/
Re: [GENERAL] pg_upgrade could not connect to server
so when I try to run pg_ctl start I get this now: c:\Program Files\PostgreSQL\9.3\bin>pg_ctl --pgdata=E:\PGSQLData start server starting c:\Program Files\PostgreSQL\9.3\bin>2015-01-22 17:25:22 PST *PANIC: could not open control file "global/pg_control": Permission denied* so looks like that is the source of the problem. any ideas? I'm also not sure why this mailing list doesn't use the mailing list's address for Reply-To. weird... On 1/22/2015 11:30 AM, Igal @ getRailo.org wrote: > same result :( > > C:\Windows\system32>"C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l > "c:\pg_upgrade_ctl.log" -D "E:\PGSQLData" -o "-p 50432 -b " start > waiting for server to startAccess is denied. > stopped waiting > pg_ctl: could not start server > Examine the log output. > > can't find any log output either. I expected it to be at > c:\pg_upgrade_ctl.log given the command above. > > > On 1/22/2015 11:14 AM, Jimmy Jack wrote: >> runas administrator >> >> https://msdn.microsoft.com/en-us/library/bb385791.aspx >> >> "The new security model does not grant administrative privileges at >> all times. Even administrators run under standard privileges when >> they perform non-administrative tasks that do not require elevated >> privileges…" >> >> >> >> On Thu, Jan 22, 2015 at 10:55 AM, Igal @ getRailo.org >> mailto:i...@getrailo.org>> wrote: >> >> Jimmy, >> >> On 1/22/2015 10:53 AM, Jimmy Jack wrote: >> > Did you try to use runas windows command? Should not make any >> > difference based on your comment how you run it. >> > >> > https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx >> > >> runas what? there is no "postgres" user on my system. the Windows >> service runs as Network Service >> >> thanks, >> >> -- >> Igal Sapir >> Railo Core Developer >> http://getRailo.org/ >> >> > > -- > Igal Sapir > Railo Core Developer > http://getRailo.org/ -- Igal Sapir Railo Core Developer http://getRailo.org/
Re: [GENERAL] implicit cast works for insert, not for select
On Thu, Jan 22, 2015 at 12:25 PM, Tom Lane wrote: > You realize of course that you've set that to be an assignment cast, > not an implicit cast as the title of your message suggests. So this > only changes the behavior for assignment contexts, ie INSERT/UPDATE > target values. > Oops, my intent was to make it implicit so it works permanently on not just INSERT/UPDATE, but also SELECT. Thanks for the explanation as that helped fixed the select. I just need to use: atdev=# update pg_cast set castcontext = 'i' where castsource ='int'::regtype and casttarget = 'bool'::regtype; >> HINT: No operator matches the given name and argument type(s). You >> might need to add explicit type casts. > > Well, yeah. If you made int->bool be an implicit cast instead, this > would work. The side-effects of that might be more painful than fixing > your application would be, however. It's quite likely that other > cases involving mixtures of int and bool, or operators/functions that > exist for both types, would suddenly start throwing "ambiguous > operator" errors. > > I wonder whether you've made sure that (a) you're using a current > release of Hibernate, and (b) it knows that it's talking to Postgres > and not SQL Server. The alleged advantage of ORMs is that they can > adapt their queries to the target database. Fixing this sort of > non-standard, non-portable query at the database level is entirely > the wrong way to go about it, IMO. > > regards, tom lane That was a query using the postgres hibernate dialect. It expects a zero and one int for booleans like SQL Server does, because the postgres db population was done that way. The equivalent SQL Server hibernate generated query is quite different. My thinking is that this cast is the only viable option, compared to changing 0 and 1 to '0' and '1' a couple of million times in a big DDL, on a couple hundred boolean columns. I'd need a special regex for every table that'd be a nightmare to maintain. So I'm good for now, thanks all. -- 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] BDR Error restarted
On 23 January 2015 at 08:22, agent wrote: > Hi Craig I have a similar issue with a rather small number of servers > involved. > OK. That looks odd. What revision are you running exactly? git rev-parse --short HEAD please. Also, correponding logs from the other two nodes please.
Re: [GENERAL] BDR Error restarted
Hi CraigI have a similar issue with a rather small number of servers involved.*Node 1:*max_replication_slots = 4max_wal_senders = 6wal_level = 'logical'track_commit_timestamp = onshared_preload_libraries = 'bdr'max_worker_processes = 10log_error_verbosity = verboselog_min_messages = debug1log_line_prefix = 'd=%d p=%p a=%a%q 'bdr.default_apply_delay = 2000bdr.log_conflicts_to_table = onbdr.connections = 'bdrnode3,bdrnode3'bdr.bdrnode2_dsn = 'dbname=db user=postgres host=host2 port=5599'bdr.bdrnode3_dsn = 'dbname=db user=postgres host=host3 port=5599'*Node 2:*max_replication_slots = 4max_wal_senders = 6wal_level = 'logical'track_commit_timestamp = onshared_preload_libraries = 'bdr'max_worker_processes = 10log_error_verbosity = verboselog_min_messages = debug1log_line_prefix = 'd=%d p=%p a=%a%q 'bdr.default_apply_delay = 2000bdr.log_conflicts_to_table = onbdr.connections = 'bdrnode1,bdrnode3'bdr.bdrnode1_dsn = 'dbname=db user=postgres host=host1 port=5599'bdr.bdrnode3_dsn = 'dbname=db user=postgres host=host3 port=5599'bdr.bdrnode1_init_replica = onbdr.bdrnode1_replica_local_dsn = 'dbname=db user=postgres host=localhost port=5599'*Node 3:*max_replication_slots = 4max_wal_senders = 6wal_level = 'logical'track_commit_timestamp = onshared_preload_libraries = 'bdr'max_worker_processes = 10log_error_verbosity = verboselog_min_messages = debug1log_line_prefix = 'd=%d p=%p a=%a%q 'bdr.default_apply_delay = 2000bdr.log_conflicts_to_table = onbdr.connections = 'bdrnode1,bdrnode2'bdr.bdrnode1_dsn = 'dbname=db user=postgres host=host1 port=5599'bdr.bdrnode2_dsn = 'dbname=db user=postgres host=host2 port=5599'bdr.bdrnode1_init_replica = onbdr.bdrnode1_replica_local_dsn = 'dbname=db user=postgres host=localhost port=5599'The above setup is an attempt for a "3-remote site simple Multi-Master Plex" configuration.Node1 and Node2 are replicating, but Node3 does not work, and the following data is is from Node3 logs:d=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive CONTEXT: slot "bdr_16396_6106457559585933042_1_16398__", output plugin "bdr", in the startup callbackd=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive LOCATION: pg_decode_startup, bdr_output.c:450d=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive ERROR: 55000: bdr output plugin: slot creation rejected, bdr.bdr_nodes entry for local node (sysid=6107134089288229725, timelineid=1, dboid=16396): status='i', bdr still starting up: applying initial dump of remote noded=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive HINT: Monitor pg_stat_activity and the logs, wait until the node has caught upd=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive CONTEXT: slot "bdr_16396_6106457559585933042_1_16398__", output plugin "bdr", in the startup callbackd=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive LOCATION: bdr_ensure_node_ready, bdr_output.c:268d=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive LOG: 08006: could not receive data from client: Connection reset by peerd=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive LOCATION: pq_recvbuf, pqcomm.c:869d=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive DEBUG: 08003: unexpected EOF on client connectiond=db_name p=3173 a=bdr (6106457559585933042,1,16398,): receive LOCATION: SocketBackend, postgres.c:353d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive DEBUG: 0: received replication command: IDENTIFY_SYSTEMd=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive LOCATION: exec_replication_command, walsender.c:1292d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive DEBUG: 0: received replication command: START_REPLICATION SLOT "bdr_16396_6106458869483394081_1_16396__" LOGICAL 0/0 (pg_version '90400', pg_catversion '201408161', bdr_version '701', min_bdr_version '700', sizeof_int '4', sizeof_long '8', sizeof_datum '8', maxalign '8', float4_byval '1', float8_byval '1', integer_datetimes '1', bigendian '0', db_encoding 'UTF8')d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive LOCATION: exec_replication_command, walsender.c:1292d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive DEBUG: 0: bdr.bdr_conflict_handlers OID set to 16991d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive CONTEXT: slot "bdr_16396_6106458869483394081_1_16396__", output plugin "bdr", in the startup callbackd=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive LOCATION: pg_decode_startup, bdr_output.c:450d=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive ERROR: 55000: bdr output plugin: slot creation rejected, bdr.bdr_nodes entry for local node (sysid=6107134089288229725, timelineid=1, dboid=16396): status='i', bdr still starting up: applying initial dump of remote noded=db_name p=3175 a=bdr (6106458869483394081,1,16396,): receive HINT: Monitor pg_stat_activity and the logs, wait until the node has caught upd=db_name p=3175 a=bdr (610645886948
[GENERAL] how to duplicate data for few times by SQL command in PG
Hi I have a table which just has one column as following in Original Table. I wanna duplicate all of data for few times and with same order as following in New Table. Is there anyone who can help me? Thanks Han Original Table 23 45 65 22 New Table 23 23 23 45 45 45 65 65 65 65 22 22 22 22
Re: [GENERAL] Getting a delta of two json-typed objects? (a breeze w/ hstore)
Hi, not so long ago, I've asked myself the same question. I've written two
fuction that do this:
SELECT build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each($1) old
LEFT OUTER JOIN json_each($2) new ON old.key = new.key
WHERE old.value::text <> new.value::text OR new.key IS NULL
HAVING array_agg(to_json(old.key)) IS NOT NULL
AND array_agg(old.value) IS NOT NULL;
and this:
CREATE OR REPLACE FUNCTION pgmemento.build_json(
json_keys ANYARRAY,
json_values ANYARRAY
) RETURNS JSON AS
$$
DECLARE
json_string TEXT := '{';
delimeter TEXT := '';
json_result JSON;
BEGIN
FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP
json_string := json_string || delimeter || json_keys[i] || ':' ||
json_values[i];
delimeter := ',';
END LOOP;
json_string := json_string || '}';
EXECUTE format('SELECT %L::json', json_string) INTO json_result;
RETURN json_result;
END
$$
LANGUAGE plpgsql;
Not the best way actually. I wonder, how I could benefit from the new
build_json function in 9.4 json_build_object(VARIADIC "any"). Have to get my
keys and values in alternating order... hm.
Then I've also found this nice example, which might do the things you are
looking for:
http://schinckel.net/2014/05/25/querying-json-in-postgres/
Ahoi
Felix
Gesendet: Donnerstag, 22. Januar 2015 um 20:37 Uhr
Von: "Wells Oliver"
An: "pgsql-general@postgresql.org"
Betreff: [GENERAL] Getting a delta of two json-typed objects? (a breeze w/
hstore)
Hey all. I have a trigger function which does a delta of two hstore values just
doing a - b; this check is performed to see if there's a delta and if not I
don't log it.
I'm wondering if there's a suitable method for comparison two json objects? I
don't have 9.4 yet so I can't use jsonb, but if there's any input here I'd
appreciate it.
Thanks.
--
Wells Oliver
wellsoli...@gmail.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] Getting a delta of two json-typed objects? (a breeze w/ hstore)
Hi it should to work for JSON too http://8kb.co.uk/blog/2015/01/16/wanting-for-a-hstore-style-delete-operator-in-jsonb/ Regards Pavel 2015-01-22 20:37 GMT+01:00 Wells Oliver : > Hey all. I have a trigger function which does a delta of two hstore values > just doing a - b; this check is performed to see if there's a delta and if > not I don't log it. > > I'm wondering if there's a suitable method for comparison two json > objects? I don't have 9.4 yet so I can't use jsonb, but if there's any > input here I'd appreciate it. > > Thanks. > > -- > Wells Oliver > wellsoli...@gmail.com >
[GENERAL] Getting a delta of two json-typed objects? (a breeze w/ hstore)
Hey all. I have a trigger function which does a delta of two hstore values just doing a - b; this check is performed to see if there's a delta and if not I don't log it. I'm wondering if there's a suitable method for comparison two json objects? I don't have 9.4 yet so I can't use jsonb, but if there's any input here I'd appreciate it. Thanks. -- Wells Oliver wellsoli...@gmail.com
Re: [GENERAL] pg_upgrade could not connect to server
same result :( C:\Windows\system32>"C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "c:\pg_upgrade_ctl.log" -D "E:\PGSQLData" -o "-p 50432 -b " start waiting for server to startAccess is denied. stopped waiting pg_ctl: could not start server Examine the log output. can't find any log output either. I expected it to be at c:\pg_upgrade_ctl.log given the command above. On 1/22/2015 11:14 AM, Jimmy Jack wrote: > runas administrator > > https://msdn.microsoft.com/en-us/library/bb385791.aspx > > "The new security model does not grant administrative privileges at > all times. Even administrators run under standard privileges when they > perform non-administrative tasks that do not require elevated privileges…" > > > > On Thu, Jan 22, 2015 at 10:55 AM, Igal @ getRailo.org > mailto:i...@getrailo.org>> wrote: > > Jimmy, > > On 1/22/2015 10:53 AM, Jimmy Jack wrote: > > Did you try to use runas windows command? Should not make any > > difference based on your comment how you run it. > > > > https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx > > > runas what? there is no "postgres" user on my system. the Windows > service runs as Network Service > > thanks, > > -- > Igal Sapir > Railo Core Developer > http://getRailo.org/ > > -- Igal Sapir Railo Core Developer http://getRailo.org/
Re: [GENERAL] How to create a specific table
On 1/22/2015 6:54 AM, Pierre Hsieh wrote: 1. just one column which type is integer in table 2. this columns only has 1 and 2 for 50 times as following note that tables are unordered sets, the rows of a table have no implied order.1 1 1 1 1 2 2 2 2 2 is the same table as 1 2 1 2 1 2 1 2 1 2 ... -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] pg_upgrade could not connect to server
runas administrator https://msdn.microsoft.com/en-us/library/bb385791.aspx "The new security model does not grant administrative privileges at all times. Even administrators run under standard privileges when they perform non-administrative tasks that do not require elevated privileges…" On Thu, Jan 22, 2015 at 10:55 AM, Igal @ getRailo.org wrote: > Jimmy, > On 1/22/2015 10:53 AM, Jimmy Jack wrote: >> Did you try to use runas windows command? Should not make any >> difference based on your comment how you run it. >> >> https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx >> > runas what? there is no "postgres" user on my system. the Windows > service runs as Network Service > thanks, > -- > Igal Sapir > Railo Core Developer > http://getRailo.org/
Re: [GENERAL] pg_upgrade could not connect to server
maybe we can isolate the issue by breaking it into steps. this seems to cause the problem: C:\Program Files\PostgreSQL\9.4\bin>"C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_ctl.log" -D "E:\PGSQLData" -o "-p 50432 -b " start waiting for server to startAccess is denied. stopped waiting pg_ctl: could not start server Examine the log output. does that get us anywhere? On 1/22/2015 10:53 AM, Jimmy Jack wrote: > Did you try to use runas windows command? Should not make any > difference based on your comment how you run it. > > https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx > -- Igal Sapir Railo Core Developer http://getRailo.org/ -- 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] pg_upgrade could not connect to server
Jimmy, On 1/22/2015 10:53 AM, Jimmy Jack wrote: > Did you try to use runas windows command? Should not make any > difference based on your comment how you run it. > > https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx > runas what? there is no "postgres" user on my system. the Windows service runs as Network Service thanks, -- Igal Sapir Railo Core Developer http://getRailo.org/ -- 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] pg_upgrade could not connect to server
Did you try to use runas windows command? Should not make any difference based on your comment how you run it. https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx On Thu, Jan 22, 2015 at 10:48 AM, Igal @ getRailo.org wrote: > Bruce, > On 1/22/2015 10:38 AM, Bruce Momjian wrote: >> On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote: >>> >>> pg_upgrade_server_start.log contains the following: >>> >>> command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l >>> "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> >>> "pg_upgrade_server_start.log" 2>&1 >>> Access is denied. >>> waiting for server to start stopped waiting >> Yep, that's the problem all right. The question is why is it failing? > Thank you for your prompt reply. > I am running Windows Server 2008 R2 64bit. The commands are run using > the Administrator account. There is no Domain Controller there. This > is pretty much a standalone server in a simple Workgroup network. > Is there any other information I can provide to help figure this out? > -- > Igal Sapir > Railo Core Developer > http://getRailo.org/ > -- > 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] pg_upgrade could not connect to server
On Thu, Jan 22, 2015 at 10:46:50AM -0800, Igal @ getRailo.org wrote: > Bruce, > > On 1/22/2015 10:38 AM, Bruce Momjian wrote: > > On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote: > >> > >> pg_upgrade_server_start.log contains the following: > >> > >> command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l > >> "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> > >> "pg_upgrade_server_start.log" 2>&1 > >> Access is denied. > >> waiting for server to start stopped waiting > > Yep, that's the problem all right. The question is why is it failing? > > Thank you for your prompt reply. > > I am running Windows Server 2008 R2 64bit. The commands are run using > the Administrator account. There is no Domain Controller there. This > is pretty much a standalone server in a simple Workgroup network. > > Is there any other information I can provide to help figure this out? I have no ideas. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade could not connect to server
Bruce, On 1/22/2015 10:38 AM, Bruce Momjian wrote: > On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote: >> >> pg_upgrade_server_start.log contains the following: >> >> command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l >> "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> >> "pg_upgrade_server_start.log" 2>&1 >> Access is denied. >> waiting for server to start stopped waiting > Yep, that's the problem all right. The question is why is it failing? Thank you for your prompt reply. I am running Windows Server 2008 R2 64bit. The commands are run using the Administrator account. There is no Domain Controller there. This is pretty much a standalone server in a simple Workgroup network. Is there any other information I can provide to help figure this out? -- Igal Sapir Railo Core Developer http://getRailo.org/ -- 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] pg_upgrade could not connect to server
On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote: > > could not connect to old postmaster started with the command: > > "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_server.log" > > -D "E:\PGSQLData" -o "-p 50432 -b " start > > > > any help would be appreciated. > > > > thanks! > pg_upgrade_server_start.log contains the following: > > command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l > "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> > "pg_upgrade_server_start.log" 2>&1 > Access is denied. > waiting for server to start stopped waiting Yep, that's the problem all right. The question is why is it failing? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade could not connect to server
On Thu, Jan 22, 2015 at 10:22:34AM -0800, Igal @ getRailo.org wrote: > Performing Consistency Checks > - > Checking cluster versions ok > > *failure* > Consult the last few lines of "pg_upgrade_server_start.log" or > "pg_upgrade_server.log" for > the probable cause of the failure. > > connection to database failed: could not connect to server: Connection > refused (0x274D/10061) > Is the server running on host "localhost" (::1) and accepting > TCP/IP connections on port 50432? > could not connect to server: Connection refused (0x274D/10061) > Is the server running on host "localhost" (127.0.0.1) and accepting > TCP/IP connections on port 50432? > > could not connect to old postmaster started with the command: > "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D > "E:\PGSQLData" -o "-p 50432 -b " start Can you look higher up in the log file? Does this recent Windows pg_upgrade failure report match yours at all? http://www.postgresql.org/message-id/flat/CAEB4t-OHNE95=n5u4yssykwipqswequtbsjkayj63_1vzkz...@mail.gmail.com#CAEB4t-OHNE95=n5u4yssykwipqswequtbsjkayj63_1vzkz...@mail.gmail.com -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade could not connect to server
On 1/22/2015 10:22 AM, Igal @ getRailo.org wrote: > hi all, > > I'm trying to follow the pg_upgrade docs from > http://www.postgresql.org/docs/9.4/static/pgupgrade.html > > I'm on Windows 64bit, and experience a few issues: > > 1) I do not have a "postgres" user account. the services are run by the > Network Service account. > > 2) minor: the service names include "-x64" suffix which is really > unnecessary and makes the docs stray from the implementation. > > 3) the main problem, possibly related to issue 1 above, is the following > error message: > > Performing Consistency Checks > - > Checking cluster versions ok > > *failure* > Consult the last few lines of "pg_upgrade_server_start.log" or > "pg_upgrade_server.log" for > the probable cause of the failure. > > connection to database failed: could not connect to server: Connection > refused (0x274D/10061) > Is the server running on host "localhost" (::1) and accepting > TCP/IP connections on port 50432? > could not connect to server: Connection refused (0x274D/10061) > Is the server running on host "localhost" (127.0.0.1) and accepting > TCP/IP connections on port 50432? > > could not connect to old postmaster started with the command: > "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D > "E:\PGSQLData" -o "-p 50432 -b " start > > any help would be appreciated. > > thanks! pg_upgrade_server_start.log contains the following: command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start >> "pg_upgrade_server_start.log" 2>&1 Access is denied. waiting for server to start stopped waiting -- Igal Sapir Railo Core Developer http://getRailo.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_upgrade could not connect to server
hi all, I'm trying to follow the pg_upgrade docs from http://www.postgresql.org/docs/9.4/static/pgupgrade.html I'm on Windows 64bit, and experience a few issues: 1) I do not have a "postgres" user account. the services are run by the Network Service account. 2) minor: the service names include "-x64" suffix which is really unnecessary and makes the docs stray from the implementation. 3) the main problem, possibly related to issue 1 above, is the following error message: Performing Consistency Checks - Checking cluster versions ok *failure* Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for the probable cause of the failure. connection to database failed: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 50432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 50432? could not connect to old postmaster started with the command: "C:\Program Files\PostgreSQL\9.3\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:\PGSQLData" -o "-p 50432 -b " start any help would be appreciated. thanks! -- Igal Sapir Railo Core Developer http://getRailo.org/ -- 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] Problem with unixODBC and PostgreSQL
On 01/22/2015 08:35 AM, Julie Reier wrote: Hi. I am hoping that someone can advise me on an issue I am having with connecting to a postgresql DB via unixODBC. I am using an ubuntu VM to test the configuration. Here’s what I did: 1. Installed unixODBC 2. Installed odbc-postgresql 3. Configured odbc drivers and data sources. I am able to connect to the database via the psql command. However, osql is failing. Here’s the output: rnduser@rnduser-VirtualBox:~$ osql -S rndredshift6 -U readonly -P *** checking shared odbc libraries linked to isql for default directories... strings: '': No such file trying /tmp/sql ... no trying /tmp/sql ... no trying /etc ... OK checking odbc.ini files reading /home/rnduser/.odbc.ini [rndredshift6] found in /home/rnduser/.odbc.ini found this section: [rndredshift6] Driver = PostgreSQL Description = data source for rndredshift Server = 10.191.4.97 Port= 5439 Database= prod looking for driver for DSN [rndredshift6] in /home/rnduser/.odbc.ini found driver line: "Driver = PostgreSQL" driver "PostgreSQL" found for [rndredshift6] in .odbc.ini found driver named "PostgreSQL" "PostgreSQL" is not an executable file looking for entry named [PostgreSQL] in /etc/odbcinst.ini found driver line: "Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so" found driver /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so for [PostgreSQL] in odbcinst.ini /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so is an executable file "Server" found, not using freetds.conf Server is "10.191.4.97" looking up hostname for ip address 10.191.4.97 osql: warning: no DNS hostname found for "10.191.4.97" Usage: host [-aCdlriTwv] [-c class] [-N ndots] [-t type] [-W time] [-R number] [-m flag] hostname [server] -a is equivalent to -v -t ANY -c specifies query class for non-IN data -C compares SOA records on authoritative nameservers -d is equivalent to -v -l lists all hosts in a domain, using AXFR -i IP6.INT reverse lookups -N changes the number of dots allowed before root lookup is done -r disables recursive processing -R specifies number of retries for UDP packets -s a SERVFAIL response should stop query -t specifies the query type -T enables TCP/IP mode -v enables verbose output -w specifies to wait forever for a reply -W specifies how long to wait for a reply -4 use IPv4 query transport only -6 use IPv6 query transport only -m set memory debugging flag (trace|record|usage) osql: no IP address found for "" rnduser@rnduser-VirtualBox:~$ Telnet to 10.191.4.97 on port 5439 works. Any ideas of what’s going on here? looking up hostname for ip address 10.191.4.97 osql: warning: no DNS hostname found for "10.191.4.97" Looks like osql is trying to do a reverse DNS lookup and fails to find a hostname. Not sure why that is a problem, but this looks to be a FreeTDS issue. You will probably find the answer faster here: http://lists.ibiblio.org/mailman/listinfo/freetds Thanks, Julie *This email is intended only for the use of the individual(s) to whom it is addressed. If you have received this communication in error, please immediately notify the sender and delete the original email.* -- Adrian Klaver adrian.kla...@aklaver.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] temporary tables are logged somehow?
It means, that 8192 commits + 8192 "create temp table" (and drop it after closing connection) costs me 48 MB of WAL files. And there is no way to reduce disk space usage, right? Does amount of data which has to be written to WAL-file depend on size of transaction? On 22 January 2015 at 18:44, Tom Lane wrote: > Michael Paquier writes: > > On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko > wrote: > >> 3. They are not WAL-logged. > >> Whats wrong with it in my case? > > > Nothing. Temporary tables are not WAL-logged, but transaction commit is. > > Right. I think there is some optimization for transactions that only > wrote temp-table data, but it could at most be skipping fsync of the > transaction commit record, not omitting generating it entirely. > > Also, changes to the system catalogs are always WAL-logged; so creation > and deletion of a temp table cause some amount of WAL output, even if > manipulation of the table's contents does not. > > regards, tom lane > -- Regards, Andrey Lizenko
[GENERAL] Problem with unixODBC and PostgreSQL
Hi. I am hoping that someone can advise me on an issue I am having with connecting to a postgresql DB via unixODBC. I am using an ubuntu VM to test the configuration. Here’s what I did: 1. Installed unixODBC 2. Installed odbc-postgresql 3. Configured odbc drivers and data sources. I am able to connect to the database via the psql command. However, osql is failing. Here’s the output: rnduser@rnduser-VirtualBox:~$ osql -S rndredshift6 -U readonly -P *** checking shared odbc libraries linked to isql for default directories... strings: '': No such file trying /tmp/sql ... no trying /tmp/sql ... no trying /etc ... OK checking odbc.ini files reading /home/rnduser/.odbc.ini [rndredshift6] found in /home/rnduser/.odbc.ini found this section: [rndredshift6] Driver = PostgreSQL Description = data source for rndredshift Server = 10.191.4.97 Port= 5439 Database= prod looking for driver for DSN [rndredshift6] in /home/rnduser/.odbc.ini found driver line: " Driver = PostgreSQL" driver "PostgreSQL" found for [rndredshift6] in .odbc.ini found driver named "PostgreSQL" "PostgreSQL" is not an executable file looking for entry named [PostgreSQL] in /etc/odbcinst.ini found driver line: " Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so" found driver /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so for [PostgreSQL] in odbcinst.ini /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so is an executable file "Server" found, not using freetds.conf Server is "10.191.4.97" looking up hostname for ip address 10.191.4.97 osql: warning: no DNS hostname found for "10.191.4.97" Usage: host [-aCdlriTwv] [-c class] [-N ndots] [-t type] [-W time] [-R number] [-m flag] hostname [server] -a is equivalent to -v -t ANY -c specifies query class for non-IN data -C compares SOA records on authoritative nameservers -d is equivalent to -v -l lists all hosts in a domain, using AXFR -i IP6.INT reverse lookups -N changes the number of dots allowed before root lookup is done -r disables recursive processing -R specifies number of retries for UDP packets -s a SERVFAIL response should stop query -t specifies the query type -T enables TCP/IP mode -v enables verbose output -w specifies to wait forever for a reply -W specifies how long to wait for a reply -4 use IPv4 query transport only -6 use IPv6 query transport only -m set memory debugging flag (trace|record|usage) osql: no IP address found for "" rnduser@rnduser-VirtualBox:~$ Telnet to 10.191.4.97 on port 5439 works. Any ideas of what’s going on here? Thanks, Julie This email is intended only for the use of the individual(s) to whom it is addressed. If you have received this communication in error, please immediately notify the sender and delete the original email.
Re: [GENERAL] how to duplicate data for few times by SQL command in PG
Hi Han, Here is an example: create table foo (v integer); insert into foo values (23), (45), (65), (22); create table bar (v integer); insert into bar select v from foo, generate_series(1,5); But note that in any relational database there is no defined order for the rows. A table is more like a set than a list. If you want order you must specify it with an ORDER BY clause. It sounds like you might want to read a SQL introduction to help you get started. Good luck! Paul On Thu, Jan 22, 2015 at 7:37 AM, tsunghan hsieh wrote: > Hi > > I have a table which just has one column as following in Original Table. I > wanna duplicate all of data for few times and with same order as following > in New Table. Is there anyone who can help me? Thanks > > Han > > Original Table > 23 > 45 > 65 > 22 > > New Table > 23 > 23 > 23 > 45 > 45 > 45 > 65 > 65 > 65 > 65 > 22 > 22 > 22 > 22 -- _ Pulchritudo splendor veritatis. -- 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] how to calculate standard deviation from a table
Are you sur you don't want a moving windows (stddev on 0 to 50 , then stdev on 1 to 51) .. If you don't want moving windows your query would look like DROP TABLE IF EXISTS your_data; CREATE TABLE your_data AS SELECT s as gid , random() as your_data_value FROM generate_series(1,1) as s ; SELECT min(gid) as min_gid, max(gid) as max_gid, stddev(your_data_value) as your_stddev FROM your_data GROUP BY (gid-1)/50 ORDER BY min_gid ASC Please note that "min(gid) as min_gid, max(gid) as max_gid" and "ORDER BY min_gid ASC" are just there to help you understand the result Cheers, Rémi-C 2015-01-22 16:49 GMT+01:00 David G Johnston : > Pierre Hsieh wrote > > Hi > > > > This table just has a column which type is integer. There are one million > > data in this table. I wanna calculate standard deviation on each 50 data > > by > > order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to > > 100 Is there anyone who can give me some suggestions? Thanks > > > > Pierre > > Integer division > > David J. > > > > -- > View this message in context: > http://postgresql.nabble.com/how-to-calculate-standard-deviation-from-a-table-tp5835031p5835042.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] how to calculate standard deviation from a table
Hi Pierre, How do you know in which group each row belongs? If you don't care how the rows are grouped, you can say this: create table foo (v float); insert into foo select random() from generate_series(1, 100) s(a); select n % 50 g, stddev(v) from (select row_number() over () n, v from foo) x group by g; On the other hand if you have some way of ordering the rows you could say this: create table foo (id integer, v float); insert into foo select a, random() from generate_series(1, 100) s(a); select (n - 1) / 50 g, stddev(v), count(*) from (select row_number() over (order by id) n, v from foo) x group by g order by g; Yours, Paul On Thu, Jan 22, 2015 at 7:18 AM, Pierre Hsieh wrote: > Hi > > This table just has a column which type is integer. There are one million > data in this table. I wanna calculate standard deviation on each 50 data by > order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to > 100 Is there anyone who can give me some suggestions? Thanks > > Pierre -- _ Pulchritudo splendor veritatis. -- 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] how to calculate standard deviation from a table
Pierre Hsieh wrote > Hi > > This table just has a column which type is integer. There are one million > data in this table. I wanna calculate standard deviation on each 50 data > by > order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to > 100 Is there anyone who can give me some suggestions? Thanks > > Pierre Integer division David J. -- View this message in context: http://postgresql.nabble.com/how-to-calculate-standard-deviation-from-a-table-tp5835031p5835042.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] temporary tables are logged somehow?
Michael Paquier writes: > On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko wrote: >> 3. They are not WAL-logged. >> Whats wrong with it in my case? > Nothing. Temporary tables are not WAL-logged, but transaction commit is. Right. I think there is some optimization for transactions that only wrote temp-table data, but it could at most be skipping fsync of the transaction commit record, not omitting generating it entirely. Also, changes to the system catalogs are always WAL-logged; so creation and deletion of a temp table cause some amount of WAL output, even if manipulation of the table's contents does not. regards, tom lane -- 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] how to duplicate data for few times by SQL command in PG
On Thursday, January 22, 2015, tsunghan hsieh wrote: > Hi > > I have a table which just has one column as following in Original Table. I > wanna duplicate all of data for few times and with same order as following > in New Table. Is there anyone who can help me? Thanks > > Han > > Original Table > 23 > 45 > 65 > 22 > > New Table > 23 > 23 > 23 > 45 > 45 > 45 > 65 > 65 > 65 > 65 > 22 > 22 > 22 > 22 > Tables do not have order. Cross join. David J.
Re: [GENERAL] partitioning query planner almost always scans all tables
Spiros Ioannou writes: > It is ORDER BY measurement_time, not measurement_id, and measurement_time > is used to create the partition. So the planner should know the correct > order, but instead it seems to query tables in the wrong order. The planner does not know that, and even if it attempted to figure it out by comparing the child tables' constraints, it could not generate a plan that considered only one child table as you incorrectly imagine. What if the "latest" table turned out to be empty at runtime? The obtained plan with a Merge Append atop Index Scan Backwards nodes seems perfectly reasonable to me. This will result in fetching only the latest row within each partition, so that the work involved is O(number of partitions) not O(total number of rows). If you're not happy with that, reconsider how many partitions you really need. Newbies almost invariably create far more partitions than is a good idea for performance. In my view, if you've got more than a couple dozen, you're doing it wrong. Partitioning is, in general, not a benefit for query performance (except in a few very narrow, specialized cases); and the more partitions you have the worse the penalty. Partitioning only helps for data management, in particular being able to drop old data in bulk rather than through expensive DELETE WHERE queries. How often do you do that, and do you really need to be able to do it at a small granularity? regards, tom lane -- 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] implicit cast works for insert, not for select
"robertlazarski ." writes: > The biggest problem has been the tiny int boolean that SQL Server > uses, which I can get to work for postgres inserts by: > atdev=# update pg_cast set castcontext = 'a' where castsource = > 'int'::regtype and casttarget = 'bool'::regtype; You realize of course that you've set that to be an assignment cast, not an implicit cast as the title of your message suggests. So this only changes the behavior for assignment contexts, ie INSERT/UPDATE target values. > That allows me to apply the DDL and all is well, until I do this > select (auto generated by hibernate) : > atdev=# select atsettings0_.atSettingsID as atSettin1_12_, > atsettings0_.OBJ_VERSION as OBJ2_12_, atsettings0_.name as name12_, > atsettings0_.value as value12_, atsettings0_.description as > descript5_12_, atsettings0_.enabled as enabled12_, > atsettings0_.deleted as deleted12_ from ATSettings atsettings0_ where > (atsettings0_."deleted" = 0 OR atsettings0_."deleted" IS NULL ) and > atsettings0_.atSettingsID=1; > ERROR: operator does not exist: boolean = integer > LINE 1: ...ttings atsettings0_ where (atsettings0_."deleted" = 0 OR ats... > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. Well, yeah. If you made int->bool be an implicit cast instead, this would work. The side-effects of that might be more painful than fixing your application would be, however. It's quite likely that other cases involving mixtures of int and bool, or operators/functions that exist for both types, would suddenly start throwing "ambiguous operator" errors. I wonder whether you've made sure that (a) you're using a current release of Hibernate, and (b) it knows that it's talking to Postgres and not SQL Server. The alleged advantage of ORMs is that they can adapt their queries to the target database. Fixing this sort of non-standard, non-portable query at the database level is entirely the wrong way to go about it, IMO. regards, tom lane -- 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] implicit cast works for insert, not for select
On 01/22/2015 02:31 AM, robertlazarski . wrote: I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via regex converting the SQL Server DDL to a Postgres DDL. Both DB's need to be supported in the near term. The biggest problem has been the tiny int boolean that SQL Server uses, which I can get to work for postgres inserts by: atdev=# update pg_cast set castcontext = 'a' where castsource = 'int'::regtype and casttarget = 'bool'::regtype; Well if I am following the below correctly: http://www.postgresql.org/docs/9.3/interactive/catalog-pg-cast.html castcontext char Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases. you should be setting castcontext = 'i' atdev=# create table foo (f1 bool); CREATE TABLE atdev=# insert into foo values(1); INSERT 0 1 That allows me to apply the DDL and all is well, until I do this select (auto generated by hibernate) : atdev=# select atsettings0_.atSettingsID as atSettin1_12_, atsettings0_.OBJ_VERSION as OBJ2_12_, atsettings0_.name as name12_, atsettings0_.value as value12_, atsettings0_.description as descript5_12_, atsettings0_.enabled as enabled12_, atsettings0_.deleted as deleted12_ from ATSettings atsettings0_ where (atsettings0_."deleted" = 0 OR atsettings0_."deleted" IS NULL ) and atsettings0_.atSettingsID=1; ERROR: operator does not exist: boolean = integer LINE 1: ...ttings atsettings0_ where (atsettings0_."deleted" = 0 OR ats... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. If I quote the zero as: = '0' Then that would work, but since this 'deleted' column is a boolean type for a hibernate generated query that works fine in SQL Server, I would really like some type of cast here to make the above select work as is. Any ideas? -- Adrian Klaver adrian.kla...@aklaver.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] how to calculate standard deviation from a table
Hi This table just has a column which type is integer. There are one million data in this table. I wanna calculate standard deviation on each 50 data by order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to 100 Is there anyone who can give me some suggestions? Thanks Pierre
Re: [GENERAL] How to create a specific table
On Thu, Jan 22, 2015 at 11:59 PM, David G Johnston wrote: > Pierre Hsieh wrote >> 1. just one column which type is integer in table >> 2. this columns only has 1 and 2 for 50 times as following > use generate_series(...), the modulus operator (to determine even/odd via > %2), and +1 Yes, embedded with CREATE TABLE AS: =# create table test as select a % 2 + 1 from generate_series(1,100) as a; SELECT 100 -- Michael -- 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] temporary tables are logged somehow?
Why unlogged tables behaviour is not the same? If I try this: > create unlogged table if not exists positiontemporarytable > (pos_instrument_id integer, pos_code varchar(40)); > prepare pos_delete as delete from "positiontemporarytable"; > execute pos_delete; no WAL files created at all. On 22 January 2015 at 17:36, Michael Paquier wrote: > On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko > wrote: > >> 3. They are not WAL-logged. > > Whats wrong with it in my case? > Nothing. Temporary tables are not WAL-logged, but transaction commit is. > -- > Michael > -- Regards, Andrey Lizenko
Re: [GENERAL] How to create a specific table
On 01/22/2015 06:54 AM, Pierre Hsieh wrote: Hi, Is there anyone who can help me to create a specific table as following? Thanks The commands you will need are here: http://www.postgresql.org/docs/9.3/interactive/sql-commands.html In particular: CREATE TABLE http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html INSERT http://www.postgresql.org/docs/9.3/interactive/sql-insert.html Pierre rule: 1. just one column which type is integer in table 2. this columns only has 1 and 2 for 50 times as following 1 2 1 2 1 2 1 2 . -- Adrian Klaver adrian.kla...@aklaver.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] How to create a specific table
Pierre Hsieh wrote > Hi, > > Is there anyone who can help me to create a specific table as following? > Thanks > > Pierre > > rule: > 1. just one column which type is integer in table > 2. this columns only has 1 and 2 for 50 times as following > > 1 > 2 > 1 > 2 > 1 > 2 > 1 > 2 > . use generate_series(...), the modulus operator (to determine even/odd via %2), and +1 HTH David J. -- View this message in context: http://postgresql.nabble.com/How-to-create-a-specific-table-tp5835024p5835026.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] How to create a specific table
Hi, Is there anyone who can help me to create a specific table as following? Thanks Pierre rule: 1. just one column which type is integer in table 2. this columns only has 1 and 2 for 50 times as following 1 2 1 2 1 2 1 2 .
Re: [GENERAL] temporary tables are logged somehow?
On 01/22/2015 06:06 AM, Andrey Lizenko wrote:
Hello all,
I have a problem with growing WAL-files populating a temporary table.
After running the following script 8192 times (each in separate
connection) I can see 3*16 MB WAL files.
3 * 16 = 48MB
Say each row takes 10 bytes(an underestimate).
8000 rows * 8192 connections * 10 bytes = 655,360,000 bytes or 655.36
megabytes.
CREATE TEMP TABLE IF NOT EXISTS positiontemporarytable
(pos_instrument_id integer, pos_code varchar(40));
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
prepare pos_insert (integer, varchar(40)) as insert into
"positiontemporarytable" values($1, $2);
execute pos_insert ('76','27958');
execute pos_insert ('71','9406:58');
..
..
execute pos_insert ('74','19406:58');
COMMIT;
It was tested via pgbench:
pgbench -l -t 8192 -C -f /db/postgres/report_test.sql db_test
and simple bash script:
for i in {1..8192}
do
echo $i
psql -f /db/postgres/report_test.sql db_test
done
Results are the same. Server version 9.3.5 (it seems 9.2.4 and 9.2.9
also affected by this).
As metioned, for example, in Robert Haas blog
http://rhaas.blogspot.ru/2010/05/global-temporary-and-unlogged-tables.html
3. They are not WAL-logged.
Whats wrong with it in my case?
Nothing as far as I can see.
--
Regards, Andrey Lizenko
--
Adrian Klaver
adrian.kla...@aklaver.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] temporary tables are logged somehow?
On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko wrote: >> 3. They are not WAL-logged. > Whats wrong with it in my case? Nothing. Temporary tables are not WAL-logged, but transaction commit is. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] temporary tables are logged somehow?
Hello all,
I have a problem with growing WAL-files populating a temporary table.
After running the following script 8192 times (each in separate connection)
I can see 3*16 MB WAL files.
CREATE TEMP TABLE IF NOT EXISTS positiontemporarytable (pos_instrument_id
> integer, pos_code varchar(40));
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> prepare pos_insert (integer, varchar(40)) as insert into
> "positiontemporarytable" values($1, $2);
> execute pos_insert ('76','27958');
> execute pos_insert ('71','9406:58');
>
..
>
..
execute pos_insert ('74','19406:58');
COMMIT;
It was tested via pgbench:
> pgbench -l -t 8192 -C -f /db/postgres/report_test.sql db_test
and simple bash script:
> for i in {1..8192}
> do
> echo $i
> psql -f /db/postgres/report_test.sql db_test
> done
Results are the same. Server version 9.3.5 (it seems 9.2.4 and 9.2.9 also
affected by this).
As metioned, for example, in Robert Haas blog
http://rhaas.blogspot.ru/2010/05/global-temporary-and-unlogged-tables.html
> 3. They are not WAL-logged.
Whats wrong with it in my case?
--
Regards, Andrey Lizenko
Re: [GENERAL] partitioning query planner almost always scans all tables
> > > > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE > > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY > > measurement_time DESC LIMIT 1; > > > > This seems to fail, scanning all tables. Do you think this can be > improved > > at all ? The query plan of the above query is as follows: > > The combination of sorting by measurement_source_id and limit > hinders constraint exclusion because the order of the column > across whole the inheritance is not known to planner. And the > below plan also dosn't show whether constraint exclusion worked > or not, by the same reason. But I suppose it worked. > It is ORDER BY measurement_time, not measurement_id, and measurement_time is used to create the partition. So the planner should know the correct order, but instead it seems to query tables in the wrong order.
Re: [GENERAL] partitioning query planner almost always scans all tables
Hi, > @Kyotaro HORIGUCHI > thanks for your reply and time Kyotaro, Not at all. > Using the following query > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND > measurement_time >= '2015-01-01 00:00:00+0' LIMIT 1; > > produces this plan: > > Limit (cost=0.00..4.02 rows=1 width=67) (actual time=49.125..49.125 > rows=1 loops=1) >-> Append (cost=0.00..3644.05 rows=907 width=67) (actual > time=49.122..49.122 rows=1 loops=1) > -> Seq Scan on measurement_events (cost=0.00..0.00 rows=1 > width=966) (actual time=0.003..0.003 rows=0 loops=1) >Filter: ((measurement_time >= '2015-01-01 > 02:00:00+02'::timestamp with time zone) AND (measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)) > -> Bitmap Heap Scan on *measurement_events_p2015_01* > (cost=41.73..3546.10 rows=894 width=54) (actual time=49.119..49.119 rows=1 > loops=1) >Recheck Cond: ((measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= > '2015-01-01 02:00:00+02'::timestamp with time zone)) >-> Bitmap Index Scan on *measurement_events_p2015_01_pkey* > (cost=0.00..41.51 rows=894 width=0) (actual time=41.836..41.836 rows=997 > loops=1) > Index Cond: ((measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= > '2015-01-01 02:00:00+02'::timestamp with time zone)) > -> Index Scan using *measurement_events_p2015_02_pkey* on > *measurement_events_p2015_02* (cost=0.14..8.16 rows=1 width=966) (never > executed) >Index Cond: ((measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= > '2015-01-01 02:00:00+02'::timestamp with time zone)) > -> Index Scan using *measurement_events_p2015_03_pkey* on > *measurement_events_p2015_03* (cost=0.14..8.16 rows=1 width=966) (never > executed) >Index Cond: ((measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= > '2015-01-01 02:00:00+02'::timestamp with time zone)) > ... (cut for brevity) > > 1) Do you know if this means that the query will stop on 1st find (since it > is limit 1), or will it search all tables regardless results? It saids that only the first table was scanned because 1 row had been acquired. But it is unclear whether constraint exclusion worked. All of the table shown above seems to have the data after 2015/1/1 and match the condition of your query. It's okay if p_2014_12 and the earlier is not seen in the explain result and the order in which the tables appear seems to me suggesting it is okay. Please examine it on that ponit. > 2) To improve on the above, do you (or anyone else) have any input on this: > > to get the latest value from all tables, we were using the following query > (before partitioning): > > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY > measurement_time DESC LIMIT 1; > > This seems to fail, scanning all tables. Do you think this can be improved > at all ? The query plan of the above query is as follows: The combination of sorting by measurement_source_id and limit hinders constraint exclusion because the order of the column across whole the inheritance is not known to planner. And the below plan also dosn't show whether constraint exclusion worked or not, by the same reason. But I suppose it worked. Since constraint exclusion worked, it seems enough optmized. What kind of optimizaition do you expect? > -- > Limit (cost=5.57..9.99 rows=1 width=921) (actual time=5.361..5.361 rows=1 > loops=1) >-> Merge Append (cost=5.57..451374.16 rows=102155 width=921) (actual > time=5.359..5.359 rows=1 loops=1) > Sort Key: measurement_events.measurement_time > -> Index Scan Backward using measurement_events_pkey on > *measurement_events* (cost=0.12..8.14 rows=1 width=966) (actual > time=0.004..0.004 rows=0 loops=1) >Index Cond: (measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) > -> Index Scan Backward using *measurement_events_p2014_01_pkey* > on *measurement_events_p2014_01* (cost=0.14..8.16 rows=1 width=966) > (actual time=0.002..0.002 rows=0 loops=1) >Index Cond: (measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) > -> Index Scan Backward using *measurement_events_p2014_02_pkey* > on *measurement_events_p2014_02* (cost=0.14..8.16 rows=1 width=966) > (actual time=0.001..0.001 rows=0 loops=1) >Index Cond: (measurement_source_id = > 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) >
[GENERAL] implicit cast works for insert, not for select
I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via regex converting the SQL Server DDL to a Postgres DDL. Both DB's need to be supported in the near term. The biggest problem has been the tiny int boolean that SQL Server uses, which I can get to work for postgres inserts by: atdev=# update pg_cast set castcontext = 'a' where castsource = 'int'::regtype and casttarget = 'bool'::regtype; atdev=# create table foo (f1 bool); CREATE TABLE atdev=# insert into foo values(1); INSERT 0 1 That allows me to apply the DDL and all is well, until I do this select (auto generated by hibernate) : atdev=# select atsettings0_.atSettingsID as atSettin1_12_, atsettings0_.OBJ_VERSION as OBJ2_12_, atsettings0_.name as name12_, atsettings0_.value as value12_, atsettings0_.description as descript5_12_, atsettings0_.enabled as enabled12_, atsettings0_.deleted as deleted12_ from ATSettings atsettings0_ where (atsettings0_."deleted" = 0 OR atsettings0_."deleted" IS NULL ) and atsettings0_.atSettingsID=1; ERROR: operator does not exist: boolean = integer LINE 1: ...ttings atsettings0_ where (atsettings0_."deleted" = 0 OR ats... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. If I quote the zero as: = '0' Then that would work, but since this 'deleted' column is a boolean type for a hibernate generated query that works fine in SQL Server, I would really like some type of cast here to make the above select work as is. Any ideas? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
