Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)
>> I have set work_mem to a very low value intentionally for demonstration >> purposes: >> >> postgres=# show work_mem; >> work_mem >> -- >> 16MB >> (1 row) >> >> postgres=# show shared_buffers ; >> shared_buffers >> >> 128MB >> (1 row) >> >> >> When I run the following query ( I know that "not in" is not a good choice >> here ): >> >> postgres=# select count(user_id) from users where user_id not in ( select id >> from ids); >"NOT IN" where the predate is a table column can lead to very poor >query plans especially where the haystack is not provably known (at >plan time) to contain only not null values. By reducing work_mem, the >server has decided has to repeatedly search the table to search for >the presence of null values. Try converting the query to NOT EXISTS. Thank you, Merlin. As said I know that "not in" is not a good choice in this case but I still do not get what is going here. Why does the server repeatedly search for NULL values when I decrease work_mem and why not when increasing work_mem? Regards Daniel
Re: [GENERAL] keeping WAL after dropping replication slots
Hi, 2017-04-05 1:55 GMT+02:00 Adrian Klaver : > On 04/04/2017 07:45 AM, Tom DalPozzo wrote: > > Postgres version? > 9.6.1 > > Hi, >> I had two replication slots on my primary. Slaves off and (around 800) >> WALs kept as expected. >> > > Slaves off means?: > > You replication set up from the master to the slaves(how many?). > Then you disconnected the slaves how? > > I have 2 slaves configured with async replication but they were down when I dropped the slots. So the 800 WALs number mean you have wal_keep_segments set to 800? > No, wal_keep_segments is commented. 800 is the rough number of files I saw in xlog dir before dropping the slots. > > > I dropped those slots but over time, the system kept on adding new WALs >> without reusing them or deleting them. >> Only after shutdown and restart the system deleted those WAL files. >> Is that ok? >> regards >> Pupillo >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > Regards Pupillo
Re: [GENERAL] AEXPR_OR, AEXPR_AND is not in postgres 9.6, how can I rewrite where it used in 9.3 ?
On 4/4/17 23:28, lin wrote: > all. I use "AEXPR_OR", "AEXPR_AND" in postgres 9.3 ,now I update the > postgres version to 9.6, but the 9.6 version has not the "AEXPR_OR", > "AEXPR_AND". > How can I solve the problem ? See commit 2146f13408cdb85c738364fe8f7965209e08c6be about how the internal representation was changed. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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] Is there a point to having both a normal gist index and an exclude index?
Bruno Wolff III writes: > ... I create both a normal gist index and an exclude index using the > following: > CREATE INDEX contains ON iplocation USING gist (network inet_ops); > ALTER TABLE iplocation > ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&); > But I am wondering if it is useful to have the normal gist index for > finding netblocks containing a specific IP address, as it seems like the > exclude index should be usable for that as well. No, that manually-created index is completely redundant with the constraint index. 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
[GENERAL] AEXPR_OR, AEXPR_AND is not in postgres 9.6, how can I rewrite where it used in 9.3 ?
Hi, all. I use "AEXPR_OR", "AEXPR_AND" in postgres 9.3 ,now I update the postgres version to 9.6, but the 9.6 version has not the "AEXPR_OR", "AEXPR_AND". How can I solve the problem ? Thanks, wln
[GENERAL] Is there a point to having both a normal gist index and an exclude index?
I am trying to load a database with about 3.5 million records relating netblocks to locations. I currently don't know whether or not any of the netblocks overlap. If they don't, then I can simplify queries that find the locations of IP addresses. I create the table as follows: DROP TABLE IF EXISTS iplocation; CREATE TABLE iplocation ( network INET NOT NULL, geoname_id INT, registered_country_geoname_id INT, represented_country_geoname_id INT, is_anonymous_proxy BOOLEAN NOT NULL, is_satellite_provider BOOLEAN NOT NULL, postal_code TEXT, latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, accuracy_radius DOUBLE PRECISION ); Then I load the table with /copy. Then I create both a normal gist index and an exclude index using the following: DROP INDEX IF EXISTS contains; CREATE INDEX contains ON iplocation USING gist (network inet_ops); ANALYZE VERBOSE iplocation; ALTER TABLE iplocation ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&) ; So far the exclude index hasn't finished being created. But I am wondering if it is useful to have the normal gist index for finding netblocks containing a specific IP address, as it seems like the exclude index should be usable for that as well. -- 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] keeping WAL after dropping replication slots
On 04/04/2017 07:45 AM, Tom DalPozzo wrote: Postgres version? Hi, I had two replication slots on my primary. Slaves off and (around 800) WALs kept as expected. Slaves off means?: You replication set up from the master to the slaves(how many?). Then you disconnected the slaves how? So the 800 WALs number mean you have wal_keep_segments set to 800? I dropped those slots but over time, the system kept on adding new WALs without reusing them or deleting them. Only after shutdown and restart the system deleted those WAL files. Is that ok? regards Pupillo -- 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] getting column names
Thanks David Worked like a charm and results are correct Armand On Apr 4, 2017, at 5:00 PM, David G. Johnston wrote: > On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home) > wrote: > > But if I join back to foo1 like below I get > > select a.audit_id, a.table_name, b[1],b[2] > from > foo1 a, > (select > array( > select > column_name::text from > information_schema.columns > where > table_name=a.table_name > and > ( > column_name like '%add_by%' > or > column_name like '%add_date%' > )) b) as foo > ; > > ERROR: invalid reference to FROM-clause entry for table "a" > LINE 10: table_name=a.table_name > ^ > HINT: There is an entry for table "a", but it cannot be referenced from this > part of the query. > > https://www.postgresql.org/docs/9.6/static/sql-select.html > > Adding LATERAL before the second "from_item" should get rid of the error - > whether it results in a working and/or correct query I don't know. > > David J. >
Re: [GENERAL] Unexpected interval comparison
Kyotaro HORIGUCHI writes:
> The first attached is the revised patch and the second is
> temporary sanity check code for non-128bit environment code. (but
> works only on 128 bit environment)
This seemed to me to be probably even less correct, so I extracted
the addition and multiplication logic into a standalone test program
(attached), which compares the result of a multiplication to that
of native int128 arithmetic. I changed the order of the LinearInterval
fields to be LS-first so that I could overlay them onto an int128
result (on a little-endian machine); this is just for testing purposes
not something we must do in the finished code. I soon found cases
where it indeed fails, eg
$ ./a.out 0x7 0x7
7 * 7
result = 62 18446744004990074881
result = 3E FFF1
MISMATCH!
result = 63 18446744004990074881
result = 3F FFF1
After fooling with it for awhile, I decided that the cause of the
problems was basically not thinking carefully about the lower half
of the value being unsigned: that affects when to do carries in
the addition macro, and we also have to be careful about whether
or not to sign-extend the partial product terms. The second
attached file is a version that I can't break anymore, though I'm
not quite sure it's bug-free.
regards, tom lane
#include "postgres.h"
/*
* LinearInterval's alternative defeinition for the environments without
* int128 arithmetics. See interval_cmp_value for datails.
*/
typedef struct
{
uint64 lo; /* holds the lower 64 bits without sign */
int64 hi; /* holds significant 64 bits including a sign bit */
} LinearInterval;
typedef union LI
{
int128 i128;
LinearInterval li;
} LI;
/*
* arithmetic 32 bit extraction from int64
*
* INT64_AU32 extracts significant 32 bit of int64 as a int64, and INT64_AL32
* extracts non-siginificant 32 bit as a int64. Both macros extends sign bits
* according to the given value. The values of these macros and the parameter
* value are in the following relationship.
*
* i64 = (int64)INT64_AU32(i64) * (2^32) + (int64)INT64_AL32(i64)
*/
#define INT64_AU32(i64) ((i64) / (1LL<<32))
#define INT64_AL32(i64) (((i64) & 0x) | ((i64) < 0 ? 0x : 0))
/*
* Adds signed 65 bit integer into LinearInterval variable. If s is not zero,
* its sign is used as v's sign.
*/
#define LINEARINTERVAL_ADD_INT65(li, v, s) \
{ \
uint64 t = (uint64)(v); \
uint64 p = (li).lo; \
(li).lo += t; \
if (s < 0 || (s == 0 && v < 0)) \
(li).hi --; \
if ((li).lo < p) \
(li).hi ++; \
}
static inline LinearInterval
interval_times(int64 x, int64 y)
{
LinearInterval span = {0, 0};
int64 tmp;
/*
* perform 128 bit multiplication using 64 bit variables.
*
* x * y = ((x.hi << 32) + x.lo) * (((y.hi << 32) + y.lo)
* = (x.hi * y.hi) << 64 +
* ((x.hi * y.lo) + (x.lo * y.hi)) << 32 +
* x.lo * y.lo
*/
/* We don't bother calculation results in zero */
if (x != 0 && y != 0)
{
int64 x_u32 = INT64_AU32(x);
int64 x_l32 = INT64_AL32(x);
/* the first term */
span.hi = x_u32 * (y >> 32);
/* the second term */
tmp = x_l32 * (y >> 32)
+ x_u32 * (y & 0x);
span.hi += INT64_AU32(tmp);
/* this shift may push out MSB. supply it explicitly */
LINEARINTERVAL_ADD_INT65(span, INT64_AL32(tmp) << 32, tmp);
/* the third term */
tmp = x_l32 * (y & 0x);
LINEARINTERVAL_ADD_INT65(span, tmp, 0);
}
return span;
}
int
main(int argc, char **argv)
{
int64 x = strtol(argv[1], NULL, 0);
int64 y = strtol(argv[2], NULL, 0);
LI li;
LI li2;
printf("%lX * %lX\n", x, y);
li.li = interval_times(x, y);
printf("result = %ld %lu\n", li.li.hi, li.li.lo);
printf("result = %lX %lX\n", li.li.hi, li.li.lo);
li2.i128 = (int128) x * (int128) y;
if (li.li.hi != li2.li.hi || li.li.lo != li2.li.lo)
{
printf("MISMATCH!\n");
printf("result = %ld %lu\n", li2.li.hi, li2.li.lo);
printf("result = %lX %lX\n", li2.li.hi, li2.li.lo);
}
return 0;
}
#include "postgres.h"
/*
* LinearInterval's alternative defeinition for the environments without
* int128 arithmetics. See interval_cmp_value for datails.
*/
typedef struct
{
uint64 lo;/* holds the lower 64 bits without sign */
int64 hi;/* holds significant 64 bits including a sign
* bit */
} LinearInterval;
typedef union LI
{
int128 i128;
LinearInterval li;
} LI;
/*
* INT64_AU32 extracts the most significant 32 bits of int64 as int64, while
* INT64_AL32 extracts the least significant 32 bits as uint64.
*/
#define INT64_AU32(i64) ((i64) >> 32)
#define INT64_AL32(i64) ((i64) & UINT64CONST(0x))
/*
* Add an unsigned int64 value into a LinearInterval variable.
* First add the value to the .lo part, then check to see if a carry
* needs to be propagated into the .hi part. A carry is needed if both
* inputs have high bits set, or if just one input has high bit set
* but the new .lo part doesn't. Remember
Re: [GENERAL] getting column names
On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home) wrote: > > But if I join back to foo1 like below I get > > select a.audit_id, a.table_name, b[1],b[2] > from > foo1 a, > (select > array( > select > column_name::text from > information_schema.columns > where > table_name=a.table_name > and > ( > column_name like '%add_by%' > or > column_name like '%add_date%' > )) b) as foo > ; > > ERROR: invalid reference to FROM-clause entry for table "a" > LINE 10: table_name=a.table_name > ^ > HINT: There is an entry for table "a", but it cannot be referenced from > this part of the query. > https://www.postgresql.org/docs/9.6/static/sql-select.html Adding LATERAL before the second "from_item" should get rid of the error - whether it results in a working and/or correct query I don't know. David J.
[GENERAL] getting column names
Hi Can somebody please tell me if the below is possible ? I may not see the forest from the trees Thanks Armand levregdb=# select * from foo1; audit_id | table_name --+ 6012 | foo2 6013 | foo2 6014 | foo2 select * from foo2; levregdb=# select * from foo2; foo2_add_by | foo2_add_date -+--- (0 rows) levregdb=# \d foo2; Table "csiprev.foo2" Column | Type | Modifiers ---+---+--- foo2_add_by | character(10) | foo2_add_date | character(10) | My intention is to have an output like 6012 | foo2|foo2_add_by|foo2_add_date 6013 | foo2|foo2_add_by|foo2_add_date 6014 | foo2|foo2_add_by|foo2_add_date select a.audit_id, a.table_name, b[1],b[2] from foo1 a, (select array( select column_name::text from information_schema.columns where table_name='foo2' and ( column_name like '%add_by%' or column_name like '%add_date%' )) b) as foo ; audit_id | table_name | b | b --++-+--- 6012 | foo2 | foo2_add_by | foo2_add_date 6013 | foo2 | foo2_add_by | foo2_add_date 6014 | foo2 | foo2_add_by | foo2_add_date But if I join back to foo1 like below I get select a.audit_id, a.table_name, b[1],b[2] from foo1 a, (select array( select column_name::text from information_schema.columns where table_name=a.table_name and ( column_name like '%add_by%' or column_name like '%add_date%' )) b) as foo ; ERROR: invalid reference to FROM-clause entry for table "a" LINE 10: table_name=a.table_name ^ HINT: There is an entry for table "a", but it cannot be referenced from this part of the query. Any idea what am I doing wrong ? Can I do it ? Thanks Armand -- 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] store key name pattern search
Thank you Jeff Nice . I also looked at akeys/skeys My goal is to get the key by pattern matching and then passing it back like in col1->key Armand On Apr 4, 2017, at 12:42 PM, Jeff Janes wrote: > On Tue, Apr 4, 2017 at 8:41 AM, Armand Pirvu (home) > wrote: > Hi > > I have the following case > > > select * from foo; > col1 > - > "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", > "group_add_by"=>"557651" > "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", > "item_add_by"=>"557652" > (2 rows) > > Is there anyway I can do a pattern search by hstore key name something like > > select * from foo where skeys(col1) like '%add_by%'; > > I looked on the doc but did not see anything , or did I miss it ? > > select * from foo where array_to_string(akeys(x),';') like '%add\_by%'; > > Note that I back-slashed the underscore, otherwise it acts as a wildcard and > may match more than you bargained for. > > Cheers, > > Jeff >
Re: [GENERAL] store key name pattern search
On Tue, Apr 4, 2017 at 8:41 AM, Armand Pirvu (home) wrote: > Hi > > I have the following case > > > select * from foo; > col1 > > - > "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", > "group_add_by"=>"557651" > "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", > "item_add_by"=>"557652" > (2 rows) > > Is there anyway I can do a pattern search by hstore key name something like > > select * from foo where skeys(col1) like '%add_by%'; > > I looked on the doc but did not see anything , or did I miss it ? > select * from foo where array_to_string(akeys(x),';') like '%add\_by%'; Note that I back-slashed the underscore, otherwise it acts as a wildcard and may match more than you bargained for. Cheers, Jeff
Re: [GENERAL] WAL being written during SELECT * query
On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo wrote: > Hi, > I have a very big table (10GB). > I noticed that many WAL segments are being written when elaborating read > only transactions like this: > select * from dati256 where id >4300 limit 100; > I don't understand why are there WAL writings during read only transactions. > Regards > Pupillo I think this is the db setting hint bits, but I'm no expert in that area. -- 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] WAL being written during SELECT * query
On 04/04/17 16:46, Tom DalPozzo wrote: Hi, I have a very big table (10GB). I noticed that many WAL segments are being written when elaborating read only transactions like this: select * from dati256 where id >4300 limit 100; I don't understand why are there WAL writings during read only transactions. As far as I know (and I could be wrong), WAL segments reflect activity in the entire cluster, not just one table - so perhaps there is write activity happening in other tables, or in other databases. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] store key name pattern search
Thank you — Armand On Apr 4, 2017, at 10:50 AM, Oleg Bartunov wrote: > > On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home) > wrote: > Hi > > I have the following case > > > select * from foo; > col1 > - > "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", > "group_add_by"=>"557651" > "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", > "item_add_by"=>"557652" > (2 rows) > > Is there anyway I can do a pattern search by hstore key name something like > > select * from foo where skeys(col1) like '%add_by%'; > > I looked on the doc but did not see anything , or did I miss it ? > > No. You may convert to json and use jsquery > https://github.com/postgrespro/jsquery > > Thanks > > Armand > > > > -- > 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] store key name pattern search
On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home) wrote: > Hi > > I have the following case > > > select * from foo; > col1 > > - > "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", > "group_add_by"=>"557651" > "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", > "item_add_by"=>"557652" > (2 rows) > > Is there anyway I can do a pattern search by hstore key name something like > > select * from foo where skeys(col1) like '%add_by%'; > > I looked on the doc but did not see anything , or did I miss it ? > No. You may convert to json and use jsquery https://github.com/postgrespro/jsquery > > Thanks > > Armand > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] WAL being written during SELECT * query
Hi, I have a very big table (10GB). I noticed that many WAL segments are being written when elaborating read only transactions like this: select * from dati256 where id >4300 limit 100; I don't understand why are there WAL writings during read only transactions. Regards Pupillo
[GENERAL] store key name pattern search
Hi I have the following case select * from foo; col1 - "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", "group_add_by"=>"557651" "show_id"=>"2", "group_id"=>"33", "group_name"=>"slc", "item_add_by"=>"557652" (2 rows) Is there anyway I can do a pattern search by hstore key name something like select * from foo where skeys(col1) like '%add_by%'; I looked on the doc but did not see anything , or did I miss it ? Thanks Armand -- 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] audit function and old.column
Sorry for the delay
This is the one I used
https://wiki.postgresql.org/wiki/Audit_trigger
And the cfg_global_audit
Column | Type | Modifiers
--+--+
audit_id | bigint | not null
table_name | text | not null
show_id | bigint |
user_name| character varying(100) |
action_timestamp | timestamp with time zone | not null default now()
action | text | not null
query| text |
pflag| boolean |
pjob | bigint |
old_values | hstore |
new_values | hstore |
I have OLD and NEW that is true
I went this way
select hstore(new.*)->(select * from (select skeys(hstore(new.*)) ) as
cfg_skeys where skeys like ('%_add_by%')) into skeys_add_by;
insert into cfg_global_audit
values (audit_tx_id, tg_table_name::text, new.show_id, skeys_add_by,
current_timestamp, 'U', current_query(),'N', 0,hstore(old.*), hstore(new.*));
See the problem is that I might have group_add_by as a key, but I can also have
instead of group_add_by, item_add_by
if I would have a function that I could search in an store buy a key name
pattern matching , aka get me the value for a key like ‘%add_by%’ it would be
great
In this case I could go around like it
I will post another question related to hstore search since although it has a
common ground it is different regarding the goal
Thanks
Armand
On Mar 23, 2017, at 6:38 PM, Adrian Klaver wrote:
> On 03/23/2017 02:00 PM, Armand Pirvu (home) wrote:
>
> Try number two.
>
>> Hello
>>
>> I am using in a project the audit trigger from the wiki
>
> Witch one?
> (It seems the other spelling is banned)
>
>> One particular aspect is that not all tables that I am after have the same
>> columns .
>> And it is one in particular I am stumbling onto , and it ends i n _add_by.
>> It can be group_add_by, car_add_by and so on.
>>
>> The old value of this column I need to record in the case of an update for
>> example. The bad thing is also that it's position is not always the same.
>> Meaning it can be 4th , 2nd and so on
>>
>> Just trying to avoid to have a function for each table and a bunch of hard
>> coding which would be less than ideal
>
> So in the below what is the table schema for cfg_global_audit?
>
>>
>>
>>
>> drop function func_global_audit();
>> create or replace function func_global_audit() returns trigger as $$
>> declare
>> audit_tx_id bigint;
>> begin
>>select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id;
>>if tg_op = 'UPDATE' then
>>insert into cfg_global_audit
>>values (audit_tx_id, tg_table_name::text, current_user::text,
>> current_timestamp, 'U', current_query(),'N', hstore(old.*), hstore(new.*),
>> akeys(hstore(new.*) - hstore(old.*)));
>>return new;
>>end if;
>> end;
>> $$
>> language plpgsql security definer;
>>
>> drop trigger trig_cfg_group on cfg_group;
>> create trigger trig_cfg_group after insert or update or delete on cfg_group
>> for each row execute procedure func_global_audit();
>>
>>
>>
>> levregdb=# select old_values from cfg_global_audit;
>>
>> old_values
>>
>>
>> -
>> "show_id"=>"1", "group_id"=>"33", "group_name"=>"Region 1",
>> "group_note"=>"test1", "group_type"=>"Files", "group_add_by"=>"557651",
>> "group_add_date"=>"2016-09-28 09:52:47.672398-05", "additional_info
>> "=>NULL, "group_description"=>""
>> (1 row)
>>
>> So the idea is that I need 557651 which is the group_add_by old value
>> recorded in user_name of the audit table
>
> Don't you have the OLD values in wherever you put hstore(OLD.*)?
>
> You have 557651, so I am not sure what you mean by you need it?
>
>>
>> 1- is there any way I can look in the OLD record for such column something
>> like OLD.%add_by% ?
>> 2 - I was thinking also getting the column name which is easy and prepend
>> with old. string and then do a select old.group_add_by into myvar.
>> Apparently it does not work
>>
>> I mean yes I can do
>> select 'old.'||column_name from information_schema.columns where table_name
>> = 'cfg_group' and column_name like '%_add_by%' ;
>>
>> But
>> create or replace function func_global_audit() returns trigger as $$
>> declare
>> audit_tx_id bigint;
>> cfg_schema text;
>> cfg_by_col text;
>> cfg_by_col1 text;
>> begin
>> select current_schema into cfg_schema;
>> select nextval('seq_a
[GENERAL] keeping WAL after dropping replication slots
Hi, I had two replication slots on my primary. Slaves off and (around 800) WALs kept as expected. I dropped those slots but over time, the system kept on adding new WALs without reusing them or deleting them. Only after shutdown and restart the system deleted those WAL files. Is that ok? regards Pupillo
Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)
On Tue, Apr 4, 2017 at 8:20 AM, Daniel Westermann wrote: > Hi, > > PostgreSQL 9.6.2 on CentOS 7.3 x64. > > This is my data set: > > drop table if exists users; > drop table if exists ids; > create table users ( user_id int >, username varchar(50) >); > with generator as > ( select a.* > from generate_series (1,300) a >order by random() > ) > insert into users ( user_id > , username > ) >select a > , md5(a::varchar) > from generator; > create unique index i_users on users ( user_id ); > create table ids ( id int ); > insert into ids (id) values ( generate_series ( 250, 350 ) ); > create unique index i_ids on ids ( id ); > analyze users; > analyze ids; > > I have set work_mem to a very low value intentionally for demonstration > purposes: > > postgres=# show work_mem; > work_mem > -- > 16MB > (1 row) > > postgres=# show shared_buffers ; > shared_buffers > > 128MB > (1 row) > > > When I run the following query ( I know that "not in" is not a good choice > here ): > > postgres=# select count(user_id) from users where user_id not in ( select id > from ids); "NOT IN" where the predate is a table column can lead to very poor query plans especially where the haystack is not provably known (at plan time) to contain only not null values. By reducing work_mem, the server has decided has to repeatedly search the table to search for the presence of null values. Try converting the query to NOT EXISTS. merlin -- 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] browser interface to forums please?
Greetings, * Robin St.Clair (ro...@robinstclair.net) wrote: > Please do not encrypt what does not need to be encrypted. Signing > communications to a mailing list probably isn't required? Signing communications demonstrates that the message was, indeed, from me. You are certainly welcome to ignore it. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] browser interface to forums please?
Greetings Vinny, * vinny (vi...@xs4all.nl) wrote: > I meant it as "in an ideal world". It's a bit like buying a car > and finding out that they have not put the wheels on. It's not > difficult to put them on yourself, > but you kind of expect that the people who want you to user their > car would do that for you. There's a bit of a difference between buying a car and using a service which is provided for free from a team of volunteers. I agree that the "in an ideal world" wording is better. :) Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] browser interface to forums please?
On 2017-04-04 15:04, Stephen Frost wrote: Greetings, * vinny (vi...@xs4all.nl) wrote: And yes, I can probably setup my email to do something like that, the point is that I shouldn't have to. I'm all for improving things and adding automation where it'll help, but the infrastructure is basically run by volunteers. Making statements like "I shouldn't have to" isn't the best approach to getting the changes you'd like to see happen done. I meant it as "in an ideal world". It's a bit like buying a car and finding out that they have not put the wheels on. It's not difficult to put them on yourself, but you kind of expect that the people who want you to user their car would do that for you. Anyway, thanks for the response! Thanks! Stephen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query never completes with low work_mem (at least not within one hour)
Hi,
PostgreSQL 9.6.2 on CentOS 7.3 x64.
This is my data set:
drop table if exists users;
drop table if exists ids;
create table users ( user_id int
, username varchar(50)
);
with generator as
( select a.*
from generate_series (1,300) a
order by random()
)
insert into users ( user_id
, username
)
select a
, md5(a::varchar)
from generator;
create unique index i_users on users ( user_id );
create table ids ( id int );
insert into ids (id) values ( generate_series ( 250, 350 ) );
create unique index i_ids on ids ( id );
analyze users;
analyze ids;
I have set work_mem to a very low value intentionally for demonstration
purposes:
postgres=# show work_mem;
work_mem
--
16MB
(1 row)
postgres=# show shared_buffers ;
shared_buffers
128MB
(1 row)
When I run the following query ( I know that "not in" is not a good choice here
):
postgres=# select count(user_id) from users where user_id not in ( select id
from ids);
... this seems to never complete (at least not within one hour).
Setting work_mem to 32MB and all is fine.
top shows the session at 100% CPU. No waits are listed in pg_stat_activity:
postgres=# select pid,wait_event_type,wait_event,state,query from
pg_stat_activity ;
pid | wait_event_type | wait_event | state | query
---+-+++--
17817 | | | active | select count(user_id) from users where user_id not in (
select id from ids);
17847 | | | active | select pid,wait_event_type,wait_event,state,query from
pg_stat_activity ;
(2 rows)
strace shows more ore less always this, so something is happening:
read(14,
"\0\0\1\0\0\t\30\0\351G1\0\16\0\0\0\1\0\0\t\30\0\352G1\0\16\0\0\0\1\0"...,
8192) = 8192
read(14,
"\1\0\0\t\30\0002J1\0\16\0\0\0\1\0\0\t\30\0003J1\0\16\0\0\0\1\0\0\t"..., 8192)
= 8192
read(14, "\0\t\30\0{L1\0\16\0\0\0\1\0\0\t\30\0|L1\0\16\0\0\0\1\0\0\t\30\0"...,
8192) = 8192
read(14,
"\30\0\304N1\0\16\0\0\0\1\0\0\t\30\0\305N1\0\16\0\0\0\1\0\0\t\30\0\306N"...,
8192) = 8192
read(14, "\rQ1\0\16\0\0\0\1\0\0\t\30\0\16Q1\0\16\0\0\0\1\0\0\t\30\0\17Q1\0"...,
8192) = 8192^C
postgres@pgbox:/u02/pgdata/PG962/ [PG962] ls -la /proc/17817/fd/
total 0
dr-x--. 2 postgres postgres 0 Apr 4 14:45 .
dr-xr-xr-x. 9 postgres postgres 0 Apr 4 14:34 ..
lr-x--. 1 postgres postgres 64 Apr 4 14:45 0 -> /dev/null
l-wx--. 1 postgres postgres 64 Apr 4 14:45 1 -> pipe:[58121]
lrwx--. 1 postgres postgres 64 Apr 4 14:45 10 -> socket:[58881]
lr-x--. 1 postgres postgres 64 Apr 4 14:45 11 -> pipe:[58882]
l-wx--. 1 postgres postgres 64 Apr 4 14:45 12 -> pipe:[58882]
lrwx--. 1 postgres postgres 64 Apr 4 14:45 13 ->
/u02/pgdata/PG962/base/13323/16516
lrwx--. 1 postgres postgres 64 Apr 4 14:45 14 ->
/u02/pgdata/PG962/base/pgsql_tmp/pgsql_tmp17817.1
l-wx--. 1 postgres postgres 64 Apr 4 14:45 2 -> pipe:[58121]
lrwx--. 1 postgres postgres 64 Apr 4 14:45 3 -> anon_inode:[eventpoll]
lrwx--. 1 postgres postgres 64 Apr 4 14:45 4 ->
/u02/pgdata/PG962/base/13323/2601
lrwx--. 1 postgres postgres 64 Apr 4 14:45 5 ->
/u02/pgdata/PG962/base/13323/16517
lr-x--. 1 postgres postgres 64 Apr 4 14:45 6 -> pipe:[58120]
lrwx--. 1 postgres postgres 64 Apr 4 14:45 7 ->
/u02/pgdata/PG962/base/13323/16520
lrwx--. 1 postgres postgres 64 Apr 4 14:45 8 ->
/u02/pgdata/PG962/base/13323/16513
lrwx--. 1 postgres postgres 64 Apr 4 14:45 9 -> socket:[58126]
The size of the temp file does not change over time:
postgres@pgbox:/u02/pgdata/PG962/ [PG962] ls -lha
/u02/pgdata/PG962/base/pgsql_tmp/pgsql_tmp17817.2
-rw---. 1 postgres postgres 14M Apr 4 14:48
/u02/pgdata/PG962/base/pgsql_tmp/pgsql_tmp17817.2
What do I miss here? Shouldn't this complete with 16MB work_mem as well, even
when slower, but in less than one hour? Or is this expected?
Thanks for your help
Daniel
Re: [GENERAL] browser interface to forums please?
Greetings, * vinny (vi...@xs4all.nl) wrote: > The thing is; mailinglists are far from userfiendly if you are not > used to them. > Even in this thread several people have explained how much work they > have done to get it > into a state where they can easily work with it. Can you expect Joe > Average to do something like that > if they want to get more involved in PgSQL? I don't actually feel that it's really all that unreasonable, no. I've explained that we use mailing lists to a few different groups at different colleges I've spoken at and while there's been a bit of grousing from a few individuals, I don't recall anyone not knowing what a mailing list is or having all that bad of a reaction. I'll be speaking later this month again at GMU, so I'll make it a point to discuss it with the group there. > Now, I'm not saying the mailinglists should go, I'm saying there > should be an easier way > to access them. It should be possible to register on the site, post > a message and read replies, > without having to subscribe to the list and setup a way of dealing > with the influx of messages > that are, for the most post, simply not interesting to the average user. I don't think there's anyone who is particularly against that idea, but it's far from trivial to do and to address the possible spam which will result from that. All of the website code is open-source and improvements to it would be greatly welcomed, as long as they don't create a significant increase in the maintenance burden for the pginfra team. > I'd love to have an RSS feed that contains only new questions, so I > can just watch the popup > on my screen the way I do with the rest of the world, and not have > to deal with replies to topics that I don't care about anyway. I don't see any reason, off-hand at least, that this couldn't be provided. We already provide RSS feeds for other things and it's reasonably straight-forward. Replying to the RSS feed would require an email client though, at least for now. Perhaps that could be integrated into the 'whole mbox' download option though or something along those lines so you can pull the email/thread into your client easily to reply. > And yes, I can probably setup my email to do something like that, > the point is that I shouldn't have to. I'm all for improving things and adding automation where it'll help, but the infrastructure is basically run by volunteers. Making statements like "I shouldn't have to" isn't the best approach to getting the changes you'd like to see happen done. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] Unexpected interval comparison
On Tue, Apr 4, 2017 at 4:15 AM, Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.jp> wrote: > The previous expression intended to avoid decimal arithmetic, but > gcc optimizes the simple division better (using cmovns-add-sar) > than the current INT64_AU32 (jmp-sar) so I changed it. This > doesn't suffer overflow. > > How does this affect non-gcc compilers? Specifically I am interested in the llvm based compilers in FreeBSD. Or is this within a gcc-specific section of the header?
Re: [GENERAL] browser interface to forums please?
vinny schrieb am 04.04.2017 um 12:01: > I'd love to have an RSS feed that contains only new questions, so I can just > watch the popup > on my screen the way I do with the rest of the world, and not have to deal > with replies to topics that I don't care about anyway. You can read them as a newsgroup provided by news.gmane.org - I do it that way. Thunderbird works quite well with that. Thomas -- 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] browser interface to forums please?
On 2017-03-27 23:23, Steve Litt wrote: On Mon, 27 Mar 2017 11:31:02 +0900 Michael Paquier wrote: If you have subscribed to more mailing lists than -general, having one subfolder per list can also help a lot, grouping as well some of those having a low activity, for example: - one folder for -hackers and -hackers-cluster. - one folder for -general. - one folder for -jdbc and -odbc. - one for -bugs and -docs. - one for -jobs and -announce, etc. Something like that will make your hacking activity way easier to handle. I would bet that a lot of people around here do that. I sure do. I have a heck of a lot of email in a heck of a lot of folders, all stored in a nice, easy to drill down hierarchy. That hierarchy is maintained by the Dovecot IMAP server that runs on my desktop computer. I'm not against mailinglists at all, but I am for ease of use, especially for newcomers. Every time I tell someone about the mailinglists I then have to explain how they can subscribe, how to create folders, filters etc. And more often than not they just say forget it and go to some forum. When it comes to having a lively group discussion that focuses all minds into a supermind greater than the sum of the parts, a mailing list is the best tool. Well, in the end, it's not the fact that it's a mailinglist that makes the community great, it's just the fact that the active members share a methodof communication that they all like to use. Getting notifications of new messages is probably the single most important feature to keep discussions going and email provides that. The thing is; mailinglists are far from userfiendly if you are not used to them. Even in this thread several people have explained how much work they have done to get it into a state where they can easily work with it. Can you expect Joe Average to do something like that if they want to get more involved in PgSQL? Now, I'm not saying the mailinglists should go, I'm saying there should be an easier way to access them. It should be possible to register on the site, post a message and read replies, without having to subscribe to the list and setup a way of dealing with the influx of messages that are, for the most post, simply not interesting to the average user. I'd love to have an RSS feed that contains only new questions, so I can just watch the popup on my screen the way I do with the rest of the world, and not have to deal with replies to topics that I don't care about anyway. And yes, I can probably setup my email to do something like that, the point is that I shouldn't have to. -- 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] "Reverse" inheritance?
On 2017-04-04 09:12, Tim Uckun wrote: I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out. This doesn't bother me that much. It should. You are using tables as data, which is pretty much always a smell of bad design. I could be pedantic and ask how you would store unix version "14.5 \%funky penguin%/ rev 1,5" given that most of that name consists of characters that are not allowed in a table name. Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table namesthen you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me. Yes this might be a problem but one I could easily overcome. Sure, but why would you though? You already have everything in place for creating records, why bother creating a different system just for the unix versions? I'd go for a tree, possibly using recursive CTE's to dig it. I was thinking a window function but yea I am sure there is a way to do it with a flat table. I'm not sure you can do it with windowing actually, given that you'd have to sort every record based on a match with the previous record. But I've never tried it because CTE's make it so easy :-) On Tue, Apr 4, 2017 at 6:43 PM, vinny wrote: I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out. Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table names then you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me. I'd go for a tree, possibly using recursive CTE's to dig it. On 2017-04-04 05:19, Tim Uckun wrote: I have thought of doing something like a single table inheritance and it could be done but I thought this might be a little more elegant. On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun wrote: I am trying to make postgres tables work like an object hierarchy. As an example I have done this. I suspect you are barking up the wrong tree ;) You are probably better off incorporating something like the "ltree" type to encode the taxonomy. https://www.postgresql.org/docs/current/static/ltree.html [1] I haven't had a chance to leverage it myself but the concept it embodies is solid. David J. Links: -- [1] https://www.postgresql.org/docs/current/static/ltree.html -- 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] Unexpected interval comparison
Thank you for the comment.
At Mon, 03 Apr 2017 11:35:25 -0400, Tom Lane wrote in
<23053.1491233...@sss.pgh.pa.us>
> Kyotaro HORIGUCHI writes:
> > Ok, the attached patch changes the result type of
> > interval_cmp_value from TimeOffset(=int64) to new 128 bit
> > LinearInterval. The value is hidden under the functions
> > interval_eq/ge.../cmp and all other stuff seems to use the
> > functions.
>
> Looking at this now ... why isn't the INT64_AU32 macro just
>
> #define INT64_AU32(i64) ((i64) >> 32)
>
> ? The business with subtracting and re-adding 1 seems unnecessary, and it
> also creates a risk of overflow with the minimum possible int64 value.
It is equivalent to "i64 / (1<<32)" except for -INT64_MAX.
INT64_AU32 gives the value for the first term in the following
polynomial.
(int64)INT64_AU32(i64) * (2^32) + (int64)INT64_AL32(i64) = i64
The previous expression intended to avoid decimal arithmetic, but
gcc optimizes the simple division better (using cmovns-add-sar)
than the current INT64_AU32 (jmp-sar) so I changed it. This
doesn't suffer overflow.
-#define INT64_AU32(i64) (((i64) < 0 ? (((i64) - 1) >> 32) + 1: ((i64) >> 32)))
+#define INT64_AU32(i64) ((i64) / (1LL<<32))
In summation of terms in 128bit multiplication expression, I
noticed that the value of the second term's lower 32bit loses MSB
for certain cases. I changed LINEARINTERVAL_ADD_INT64 to accept
the MSB (as the 65th bit) separately.
The first attached is the revised patch and the second is
temporary sanity check code for non-128bit environment code. (but
works only on 128 bit environment)
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From d79fb456ed551fcaedea3eb3420fd4d8e56bd48c Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi
Date: Mon, 3 Apr 2017 19:41:09 +0900
Subject: [PATCH 1/2] Fix overflow during interval comparison.
The values in interval are compared by TimeOffset results of
interval_cmp_value but it is so narrow that overflows quite easily.
This patch widen the output of the function to 128 bit. For platforms
without 128 bit arithmetic, a pair of 64 bit intergers is used
instead.
---
src/backend/utils/adt/timestamp.c | 127 ++---
src/include/datatype/timestamp.h | 13
src/test/regress/expected/interval.out | 36 ++
src/test/regress/sql/interval.sql | 17 +
4 files changed, 184 insertions(+), 9 deletions(-)
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 4be1999..2411bd7 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2289,25 +2289,122 @@ timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
/*
* interval_relop - is interval1 relop interval2
*/
-static inline TimeOffset
+#ifdef HAVE_INT128
+static inline LinearInterval
interval_cmp_value(const Interval *interval)
{
- TimeOffset span;
+ LinearInterval span;
- span = interval->time;
- span += interval->month * INT64CONST(30) * USECS_PER_DAY;
- span += interval->day * INT64CONST(24) * USECS_PER_HOUR;
+ span = (int128)interval->time;
+ span += (int128)interval->month * INT64CONST(30) * USECS_PER_DAY;
+ span += (int128)interval->day * INT64CONST(24) * USECS_PER_HOUR;
return span;
}
+#else
+/*
+ * arithmetic 32 bit extraction from int64
+ *
+ * INT64_AU32 extracts significant 32 bit of int64 as a int64, and INT64_AL32
+ * extracts non-siginificant 32 bit as a int64. Both macros extends sign bits
+ * according to the given value. The values of these macros and the parameter
+ * value are in the following relationship.
+ *
+ * i64 = (int64)INT64_AU32(i64) * (2^32) + (int64)INT64_AL32(i64)
+ */
+#define INT64_AU32(i64) ((i64) / (1LL<<32))
+#define INT64_AL32(i64) (((i64) & 0x) | ((i64) < 0 ? 0x : 0))
+
+/*
+ * Adds signed 65 bit integer into LinearInterval variable. If s is not zero,
+ * its sign is used as v's sign.
+ */
+#define LINEARINTERVAL_ADD_INT65(li, v, s) \
+{ \
+ uint64 t = (uint64)(v); \
+ uint64 p = (li).lo; \
+ (li).lo += t; \
+ if (s < 0 || (s == 0 && v < 0)) \
+ (li).hi --; \
+ if ((li).lo < p) \
+ (li).hi ++; \
+}
+
+static inline LinearInterval
+interval_cmp_value(const Interval *interval)
+{
+ LinearInterval span = {0, 0};
+ int64 dayfraction;
+ int64 days, tmp;
+
+ /* days cannot overflow here */
+ dayfraction = interval->time % USECS_PER_DAY;
+ days = interval->time / USECS_PER_DAY;
+ days += interval->month * INT64CONST(30);
+ days += interval->day;
+
+ /* we assume arithmetic shift here */
+ Assert(-1 >> 1 == -1);
+
+ /*
+ * perform 128 bit multiplication using 64 bit variables.
+ *
+ * x * y = ((x.hi << 32) + x.lo) * (((y.hi << 32) + y.lo)
+ * = (x.hi * y.hi) << 64 +
+ * ((x.hi * y.lo) + (x.lo * y.hi)) << 32 +
+ * x.lo * y.lo
+ */
+
+ /* We don't bother calculation results in zero */
+ if (days != 0)
+ {
+ /*
+ * Here, the possible maximum number of days is 0xf_865ce7d8 and the
+ * constant USECS_PER_DAY is 0x14_1d
Re: [GENERAL] "Reverse" inheritance?
>I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out. This doesn't bother me that much. If can say create table ubuntu_17_04 inherits ubuntu and have it return all the parents data as I described it would be awesome >Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table names then you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me. Yes this might be a problem but one I could easily overcome. >I'd go for a tree, possibly using recursive CTE's to dig it. I was thinking a window function but yea I am sure there is a way to do it with a flat table. On Tue, Apr 4, 2017 at 6:43 PM, vinny wrote: > I agree with the barking up the wrong tree, building a physical tree in > tables doesn't sound right > given that you will have to create a new branch in the tree when a new > version/variation of ubuntu comes out. > > Also think about how you are going to do basic queries like listing all > known unix variants; if that is hidden in the table names > then you'll have to issue DDL queries to do the work of SELECT queries, > which just sounds wrong to me. > > I'd go for a tree, possibly using recursive CTE's to dig it. > > > On 2017-04-04 05:19, Tim Uckun wrote: > >> I have thought of doing something like a single table inheritance and it >> could be done but I thought this might be a little more elegant. >> >> On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun wrote: >>> >>> I am trying to make postgres tables work like an object hierarchy. As an example I have done this. >>> I suspect you are barking up the wrong tree ;) >>> >>> You are probably better off incorporating something like the "ltree" type >>> to encode the taxonomy. >>> >>> https://www.postgresql.org/docs/current/static/ltree.html >>> >>> I haven't had a chance to leverage it myself but the concept it embodies >>> is solid. >>> >>> David J. >>> >>> >>>
