Re: [GENERAL] Group by range in hour of day

2015-03-17 Thread Marc Mamin
7;,'2015-03-18 11:45:00'; insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00'; insert into t select '2015-03-17 21:15:00','2015-03-18 10:10:00'; insert into t select '2015-03-18 23:30:00','2015-03-19 01:30:00'; SELECT ser, SUM( case when e - ser < interval '1 hour' then e-ser --end interval when s >= ser then interval '1 hour' - (s - ser) --start interval else interval '1 hour' end ) as time_tot FROM (select e,s, generate_series(date_trunc('hour',s), date_trunc('hour',e), '1 hour') ser from t )foo group by ser order by 1 regards, Marc Mamin -- 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] Group by range in hour of day

2015-03-17 Thread Marc Mamin
5-03-16 11:05:00'; >insert into t select '2015-03-16 08:00:00','2015-03-16 11:45:00'; >insert into t select '2015-03-17 15:15:00','2015-03-18 11:45:00'; >insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00'; >in

Re: [GENERAL] :Posgres - performance problem

2015-03-26 Thread Marc Mamin
date master_items set feedback_to_de <> 'Yes' WHERE feedback_to_de IS DISTINCT FROM 'Yes' depending on the column nullable. regards, Marc Mamin -- 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] Restarting DB after moving to another drive

2015-05-11 Thread Marc Mamin
Hi, have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid ? They possibly still point to the old drive. I guess you have to correct them per hand before starting the moved DB. regards, Marc Mamin Von: pgsql-general-ow

[GENERAL] Row visibility issue with consecutive triggers, one being DEFERRED

2015-06-04 Thread Marc Mamin
Hello, The test below is running fine but if you add the trigger push_foo_tr (uncomment) then the exception is raised. It seems that this additional trigger to be called at the first place changes the deferrable status of the second one. Is this an expected behaviour ? regards, Marc Mamin

Re: [GENERAL] pg_relation_size performance issue

2015-06-04 Thread Marc Mamin
table. Have you checked the statistics and vacuum state of the catalog tables (pg_*) Maybe some of them haven't been analyzed yet as this is a freshly created DB. (and with a lot of DLL statements we regularly have to call vacuum full on part of the catalog to avoid bloating there.) You&#

[GENERAL] Re: Row visibility issue with consecutive triggers, one being DEFERRED

2015-06-04 Thread Marc Mamin
recall! this self containing case works well if I call the correct functions in the triggers :) Marc > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Marc Mamin > Sent: Donnerstag, 4. Juni 201

Re: [GENERAL] Row visibility issue with consecutive triggers, one being DEFERRED

2015-06-04 Thread Marc Mamin
> -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Donnerstag, 4. Juni 2015 15:56 > To: Marc Mamin > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Row visibility issue with consecutive triggers, > one being DEFERRED > > Marc

[GENERAL] select count(*);

2015-06-11 Thread Marc Mamin
, Marc Mamin -- 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] select count(*);

2015-06-11 Thread Marc Mamin
> > select *; > > -- > > ERROR: SELECT * with no tables specified is not valid > > > > select count(*); > > > > 1 > > > > Is this a must? and why 1? > > Hi, > > regarding the "why 1" part: > > I think that if we accept that > > chris=> select 'foo'; > ?column? > -

Re: [GENERAL] select count(*);

2015-06-11 Thread Marc Mamin
>>That's the point. * has no meaning without FROM >But COUNT(*) > >does have meaning - it means "the number of rows". which rows? :-) > It's not counting the number of columns in the row, so postgres doesn't need > to know what columns exist in the row to return a row count. >Geoff -- Sen

Re: [GENERAL] select count(*);

2015-06-11 Thread Marc Mamin
> -Original Message- > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Sent: Donnerstag, 11. Juni 2015 16:31 > To: Marc Mamin; 'Geoff Winkless'; Postgres General > Subject: Re: [GENERAL] select count(*); > > On 06/11/2015 07:17 AM, Marc Mami

[GENERAL] Counting the occurences of a substring within a very large text

2015-06-24 Thread Marc Mamin
, 'g') from mytable where id =-2146999703 )foo; ERROR: invalid memory alloc request size 1447215584 regards, Marc Mamin -- 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] Counting the occurences of a substring within a very large text

2015-06-24 Thread Marc Mamin
> -Original Message- > From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] > Sent: Mittwoch, 24. Juni 2015 13:44 > To: Marc Mamin; Postgres General > Subject: RE: Counting the occurences of a substring within a very large > text > > Marc Mamin wrote: > >

Re: [GENERAL] Counting the occurences of a substring within a very large text

2015-06-24 Thread Marc Mamin
> -Original Message- > From: Chris Mair [mailto:ch...@1006.org] > Sent: Mittwoch, 24. Juni 2015 13:26 > To: Marc Mamin; Postgres General > Subject: Re: [GENERAL] Counting the occurences of a substring within a > very large text > > > Hello, > >

Re: [GENERAL] Counting the occurences of a substring within a very large text

2015-06-24 Thread Marc Mamin
message, E'\n', > '')) > > FROM mytable WHERE id = -2146999703; > > > no, they both yeld the same error. > and this fails too, which is more annoying as it looks like a bug: SELECT replace(full_message, E'\n', '') FROM stadium_rprod.aserror

Re: [GENERAL] Counting the occurences of a substring within a very large text

2015-06-24 Thread Marc Mamin
>>On 24 June 2015 at 14:51, Marc Mamin wrote: >>note that the 345MB text only contains 635 lines. This might be the issue... >There's similar issue discussed here: >http://www.postgresql.org/message-id/6046.1353874...@sss.pgh.pa.us >Tom did seem to accept that the

[GENERAL] how to extract the page "address" from the ctid

2015-06-30 Thread Marc Mamin
tinct myColumn) as c FROM myTable GROUP BY substring(ctid::text, '^.([^,]+)') )foo GROUP BY c order by 1 desc; Is there a quicker way to extract the page reference from the ctid? regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresq

Re: [GENERAL] how to extract the page "address" from the ctid

2015-07-01 Thread Marc Mamin
> -Original Message- > From: Michael Paquier [mailto:michael.paqu...@gmail.com] > Sent: Mittwoch, 1. Juli 2015 07:05 > To: Marc Mamin > Cc: Postgres General > Subject: Re: [GENERAL] how to extract the page "address" from the ctid > > On Tue, Jun 30, 201

Re: [GENERAL] Systemd vs logging collector

2015-07-01 Thread Marc Mamin
combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. regards, Marc Mamin > > -- > Sent via pg

Re: [GENERAL] Slow index performance

2015-07-02 Thread Marc Mamin
e "extra" > "rec_isins_current_attachment" btree (attachment), tablespace > "extra" Hello, Are you sure that the column order of the PKs is the same in both tables? (attachment, isin) or (isin, attachment). When isin is at the s

[GENERAL] next postgres version on Amazon RDS ?

2015-07-06 Thread Marc Mamin
Hello, has anyone some insight on when we can expect a newest 9.4.x Version on RDS ? Or knows a better forum to ask for this? I should upgrade an application DB currently on 9.3 that might go on RDS but I'm a bit reluctant to go only on 9.4.1 ... regards, Marc Mamin

[GENERAL] Problem with ALTER TYPE, Indexes and cast

2015-07-08 Thread Marc Mamin
7;XY' as 'month2.foo'::regtype) Is there a way for it ? regards, Marc Mamin -- 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 ALTER TYPE, Indexes and cast

2015-07-08 Thread Marc Mamin
> -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Mittwoch, 8. Juli 2015 15:44 > To: Marc Mamin > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast > > Marc Mamin writes: > > Now

Re: [GENERAL] Creating table with data from a join

2015-07-14 Thread Marc Mamin
M a JOIN b on a.id = b.id WHERE a.id%4 = [0,1,2,3} I usually avoid parallel INSERTS to avoid I/O contention and random distribution within the target tables. Are you monitoring the I/O activity in your tests ? Have you tried to use only 2 parallel processes? regards, Marc Mamin >(accor

Re: [GENERAL] Index Only Scan vs Cache

2015-07-14 Thread Marc Mamin
ver got analyzed. Can this fool the query planner in a negative way ? regards, Marc Mamin >> The table blocks would fall out of cache if they're never touched. >> >>regards, tom lane >> >> > >Sweet! Thanks Tom. > > >--

Re: [GENERAL] Creating table with data from a join

2015-07-15 Thread Marc Mamin
> Marc, I am using postgres 9.4. I didn't benchmark, but intuitively the modulo > operator will force traversing every record in table "a" 4 times, as it can't > use an index. Not necessarily. seq scans can be synchronized: "This allows sequential scans of large tables to synchronize with each

[GENERAL] Drop down in connect time between 9.3.6 and 9.3.9 ?

2015-07-22 Thread Marc Mamin
$i -lt 1000 ]; do let i++; psql -c 'select 1' ; done; } >/dev/null real0m11.081s user0m0.140s sys 0m0.208s regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Any thoughts on a better approach to this query?

2015-09-05 Thread Marc Mamin
when type='A' then link end ))as ar_a, array_not_nulls(array_agg(case when type='B' then link end ))as ar_b, array_not_nulls(array_agg(case when type NOT IN ('A', 'B') then link end)) as ar_others from demo GROUP BY id ) SELECT id, a_ct, b_ct, ar_a, ar_b, ar_others, coalesce (ar_a[1], case when b_ct > 1 then ar_b[2] else ar_others[1] end ) as link_a, coalesce (ar_b[1], case when a_ct > 1 then ar_a[2] when a_ct = 0 then ar_others[2] else ar_others[1] end) as link_b, -- unused others case when a_ct + b_ct >=2 then ar_others else ar_others[3 - (a_ct + b_ct) : array_length(ar_others,1)] end || -- unused A & B case when has_a AND has_b then ar_a[2:a_ct] || ar_b[2:b_ct] when a_ct > 2 then ar_a[3:a_ct] when b_ct > 2 then ar_b[3:b_ct] end as unused FROM PREP order by id regards, Marc Mamin

Re: [GENERAL] clone_schema function

2015-09-17 Thread Marc Mamin
t take care of the column statistic targets (i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;) regards, Marc Mamin From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson Sent: Donnerstag, 17. September 2015 15:48 To: David

Re: [GENERAL] clone_schema function

2015-09-17 Thread Marc Mamin
Von: Melvin Davidson [melvin6...@gmail.com] Gesendet: Donnerstag, 17. September 2015 17:11 An: Marc Mamin Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] clone_schema function Thanks, >I'm not sure why you had trouble with the REPLACE(),

Re: [GENERAL] Best practices for aggregate table design

2015-10-07 Thread Marc Mamin
I would store your monthly data within a separate table. regards, Marc Mamin -- 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] Duplicate rows during pg_dump

2015-10-24 Thread Marc Mamin
>>remote_db=> insert into users_copy_without_indexes select * from users; >>INSERT 0 523342 >> remote_db=> select count(1) from users_copy_without_indexes where id >> = 123; >> count >>--- >> 2 >>(1 row) >>

Re: [GENERAL] Duplicate rows during pg_dump

2015-10-26 Thread Marc Mamin
> -Original Message- > From: Jim Nasby [mailto:jim.na...@bluetreble.com] > Sent: Montag, 26. Oktober 2015 01:55 > To: Marc Mamin; Adrian Klaver; Chaz Yoon; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Duplicate rows during pg_dump > > On 10/24/15 3:15

Re: [GENERAL] regexp_replace to remove sql comments

2015-10-28 Thread Marc Mamin
rrent_database()) >) > >SELECT regexp_replace(q,'/\*.*\*/','') as q /* strip off comments like >this */ <-- cannot get a regex to do this >FROM to_clean ORDER BY q Hi, Does this help ? select regexp_replace(' aaa /* x y z */ foo', '\/\

[GENERAL] row => text => row

2016-11-11 Thread Marc Mamin
rows as text,but in their (default) csv representation: select to_csv((c.*)) from foo c; regards, Marc Mamin

Re: [GENERAL] is this a known bug in 9.6?

2016-12-13 Thread Marc Mamin
as (select 1::int x), j as (select 1::int x) select max(x) from (select x from i union all select x from j) b; --- ERROR: could not find plan for CTE "i" regards, Marc Mamin -- 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_dump and quoted identifiers

2016-12-15 Thread Marc Mamin
t; in front of that > to see what gets printed: > > $ echo pg_dump -d postgres -t "\"Statuses\"" > pg_dump -d postgres -t "Statuses" Hello, Have you checked if the problem is limited to pg_dump ? Maybe you're facing a trivial mistake, like a space in the

[GENERAL] Fetching from psql procedures

2015-11-19 Thread Marc Mamin
good tutotial for a task like this ? thanks and best regards, Marc Mamin -- 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] Fetching from psql procedures

2015-11-20 Thread Marc Mamin
can try CTE Common Table Expression. It isn't possible with plpgsql :( > Theoretically you can write C extension where SRF function can returns data > continually.But plpgsql function using local stack and returns data as block. > Regards > Pavel Thanks. A simpler solution will be

Re: [GENERAL] Unique index problem

2015-12-20 Thread Marc Mamin
d_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) in the index definition, but I've never tried that and suspect the planner will also have trouble to include such an index in the plan. regards, Marc Mamin -- 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] execute same query only one time?

2016-02-09 Thread Marc Mamin
cte as (select ..), tmp as ( INSERT INTO result2 select ... from cte), SELECT ... from cte; query_2: select * from result2; regards, Marc Mamin -- 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] 9.5 - Is there any way to disable automatic rollback?

2016-04-11 Thread Marc Mamin
Hello, if you are using pgjdbc, there is a discussion about adding an option to modify this behavior: https://github.com/pgjdbc/pgjdbc/issues/423 This would simplify the migration of java projects, e.g. from oracle to postgres. regards, Marc Mamin From: pgsql-general-ow

Re: [GENERAL] Do parallel queries work with only dblink not with fdw?

2016-05-04 Thread Marc Mamin
emented that solution... something like db_link_send_query('conn0', 'CREATE UNLOGGED TABLE my_result... db_link_send_query('conn1', 'INSERT INTO my_result statement based on partitioning field'); db_link_send_query('conn2', 'INSERT INTO my_res

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-12 Thread Marc Mamin
What counts are the data and how they are arranged within the DB. Therefore they should put more focus on them, rather then think in OOM. That book is a great idea. A collection of bad code example and the SQL equivalents, strengthend with runtimes figures, may help move some minds best regards

[GENERAL] revert function for to_json ?

2016-05-23 Thread Marc Mamin
Hello, I'm looking for $subject example: how to revert select to_json(E'a\n''b'::text) I can achieve it with something like this: select json_extract_path_text(('{"a":'||to_json(E'a\n''b'::text)||'}')::json,'a') does anybody knows a less ugly solution ? regards, Marc Mamin

Re: [GENERAL] revert function for to_json ?

2016-05-23 Thread Marc Mamin
>From: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Marc Mamin >Sent: Montag, 23. Mai 2016 16:03 >To: pgsql-general@postgresql.org >Subject: [GENERAL] revert function for to_json ? > >Hello, > >I'm looking for

Re: [GENERAL] Postmaster processes taking all the CPU

2007-06-08 Thread Marc Mamin
Hello, if vacuuming does not help, you may also want to log all queries running more than x milliseconds to help localize the problem. see log_min_duration_statement= #ms in postgresql.conf (i didn't check its avaibility in older version) HTH, Marc

[GENERAL] advisory locks: documentation hint and feature request

2008-01-07 Thread Marc Mamin
d be the dafault behaviour for pg_advisory_lock_shared (..) and pg_try_advisory_lock_shared (...) --and possibly define a max number of allowed shared locks (over all sessions) pg_[try_]advisory_lock_shared_max(key bigint, max_count int) regards, Marc Mamin

[GENERAL] Question on STABLE functions limitations

2010-10-18 Thread Marc Mamin
Hello, I wonder if there are some special cases where STABLE functions should be avoided. I think for example of functions using set_config or querying the advisory locks. Thanks in advance, Marc Mamin

[GENERAL] pg_get_procdef ?

2010-10-27 Thread Marc Mamin
... Does anyone have a handmade solution for a pg_get_procdef function. I'm still on Postgres 8.3 ... Thanks for any help, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dblink_get_result issue

2010-11-10 Thread Marc Mamin
ed FROM clause rowtype SQL state: 42804 A subsequent call returns an empty result set, as described here: http://www.postgresql.org/docs/8.3/interactive/contrib-dblink-get-result .html Thanks for any help. Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To ma

Re: [GENERAL] dblink_get_result issue

2010-11-10 Thread Marc Mamin
> (Postgres 8.3.9) oops, this is actually 8.4.4 on Windows. -Original Message- From: Marc Mamin Sent: Mittwoch, 10. November 2010 18:25 To: 'pgsql-general@postgresql.org' Subject: dblink_get_result issue Hello, I'm experimenting with dblink, and have an issue with a

Re: [GENERAL] REINDEX requirement?

2010-11-11 Thread Marc Mamin
, old_ratio double precision, new_ratio double precision, old_pagecount integer, new_pagecount integer, execution_count integer, reindex_time bigint, CONSTRAINT reindex_list_pk PRIMARY KEY (nspname_oid, index_oid) ) regards, Marc Mamin From: pgsql-general-ow

Re: [GENERAL] dblink_get_result issue

2010-11-11 Thread Marc Mamin
For now I just ignore the first exception. BEGIN PERFORM * from dblink_get_result('myconn')as x (t text); EXCEPTION WHEN datatype_mismatch THEN NULL; END; PERFORM * from dblink_get_result('myconn')as x (t text); thanks, Marc Mamin -Original Message- From

Re: [GENERAL] Considering Solid State Drives

2010-11-11 Thread Marc Mamin
SSD caveats are well described here: http://www.postgresql.org/about/news.1249 HTH, Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau Sent: Donnerstag, 11. November 2010 07:42 To: Postgres General

Re: [GENERAL] Schema tool

2010-11-11 Thread Marc Mamin
Hello, may schemaspy help you ? http://schemaspy.sourceforge.net/sample/relationships.html HTH, Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Aram Fingal Sent: Donnerstag, 11. November 2010 17:51 To

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-17 Thread Marc Mamin
sum(case when fair then 1 else 0 end) as fair, => sum(case when fair then 1 end) as fair, :) regards, Marc Mamin -- 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] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-17 Thread Marc Mamin
ed table has a higher priority. The best solution in my opinion, would allow to dump/restore these 2 table types in separate processes (or threads..). (and by the way: would it be possible to choose the compress tool as an option for pg_dump) pgdump -F.. -Compress pigz -f out.dmp -f_unlogged o

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-17 Thread Marc Mamin
d the indexes - drop the live table - rename the shadow table to the visible one. Is it imaginable to use unlogged tables foe the shadow one and then enable logging after the switch ? regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-17 Thread Marc Mamin
yep, but I'll miss this as I only use WAL for crash recovery... regards, Marc Mamin -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Mittwoch, 17. November 2010 23:40 To: Marc Mamin Cc: PostgreSQL general; Josh Berkus Subject: Re: [GENERAL] Survey on backi

[GENERAL] plpgsql, Exception handling: catching the message ?

2010-11-18 Thread Marc Mamin
ail: ERROR: relation "my_share4" already exists (=> DUPLICATE TABLE) best regards, Marc Mamin -- 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] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-18 Thread Marc Mamin
> What are the use-cases for replicating unlogged tables? Hello, I guess they could be useful in cloud infrastructures. (see http://archives.postgresql.org/pgsql-general/2010-11/msg00865.php) regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] Help on explain analyze

2010-11-26 Thread Marc Mamin
Hello, did you also try defininig partial indexes? e.g. CREATE INDEX xx on task_definitions (ctrlid) WHERE (name::text = 'UseWSData') CREATE INDEX yy on ctrl_definitions (ctrlid) WHERE (name::text = 'IrrPeriodStart') HTH, Marc Mamin -Original Message- Fr

[GENERAL] RESET ROLE and search_path, Connection pool

2010-12-03 Thread Marc Mamin
y bound to the pg_backend_pid(). Here I will have to add the current_user within the GUC name to better isokate them) show search_path; "$user",public SET ROLE 'xxx'; show search_path; xxx,public RESET ROLE show search_path; xxx,public best regards, Marc Mamin

Re: [GENERAL] RESET ROLE and search_path, Connection pool

2010-12-05 Thread Marc Mamin
Hello, > Obviously each "user" could use RESET ROLE and become the super user.  Yes, this is a point not to forget, but isn't an issue in our case. best regards, Marc Mamin From: Derrick Rice [mailto:derrick.r...@gmail.com] Sent: Samstag, 4. Dezember 2010 00:21 To: Ma

Re: [GENERAL] pg_dump order of rows

2010-12-13 Thread Marc Mamin
etting a consistent dump of all your data may be an issue thought... regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] commit delay with dblink.

2011-01-06 Thread Marc Mamin
is off # - Archiving - #archive_mode = off # allows archiving to be done #archive_command = '' # command to use to archive a logfile segment #archive_timeout = 0# force a logfile segment switch after this Thanks, Marc Mamin -- Sent

[GENERAL] ERROR: type "foo_20110307_id_seq" already exists (expected relation "foo_20110307_id_seq" already exists)

2011-03-25 Thread Marc Mamin
r matches the duplicate_object exception and I'll try to catch it as well, but I wonder where this comes from. Cheers, Marc Mamin CREATE OR REPLACE FUNCTION public.createtable_foo(varchar,INT) RETURNS int4 AS $BODY$ BEGIN EXECUTE 'CREATE TABLE '

[GENERAL] operator is not unique: smallint[] @> smallint[] You might need to add explicit type casts (!)

2014-07-14 Thread Marc Mamin
elem; rangesel; contjoinsel @>; 11;10; b; f; f; 3831; 3831; 16; 3892; 0; range_contains; rangesel; contjoinsel regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

FW: [GENERAL] operator is not unique: smallint[] @> smallint[] You might need to add explicit type casts (!)

2014-07-15 Thread Marc Mamin
> -Original Message- > From: Pujol Mathieu [mailto:mathieu.pu...@realfusio.com] > Sent: Dienstag, 15. Juli 2014 08:40 > To: Marc Mamin > Subject: Re: [GENERAL] operator is not unique: smallint[] @> > smallint[] You might need to add explicit type casts (!) >

Re: [GENERAL] php password authentication failed for user ...

2014-07-16 Thread Marc Mamin
nect using the IP instead of "localhost" ? regards, Marc Mamin > > Thanks for helping. > > Regards, > basti > > Am 15.07.2014 16:46, schrieb Adrian Klaver: > > On 07/15/2014 07:17 AM, basti wrote: > >> Hello Adrian, > >> > >> Ye

Re: [GENERAL] Index usage with slow query

2014-07-25 Thread Marc Mamin
cost of detoasting seems to often be underestimated. For the case when one of your 3 first columns has a very low cardinality, you may consider adding some partial indexes. e.g.: create INDEX idx_the_geom_gist_general USING gist (the_geom) where class ='general'; create INDEX idx_

Re: [GENERAL] copy/dump database to text/csv files

2014-07-25 Thread Marc Mamin
OP EXECUTE 'select exists (select * from public.'||rec.tablename||')' into test; IF test THEN raise notice 'COPY public.% TO %.dump',rec.tablename,rec.tablename; END IF; END LOOP; END; $$ language plpgsql regards, Marc Mamin

Re: [GENERAL] How can I create null value from function call with no results?

2014-07-31 Thread Marc Mamin
kes sense in your context, but something like this could do the job: WITH SEL AS ( Your Query) SELECT * FROM SEL UNION ALL SELECT 'nothing found' WHERE NOT EXISTS ( select * from sel); regards, Marc Mamin

Re: [GENERAL] understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

2014-08-06 Thread Marc Mamin
= '428842195.338828' And as it now looks like a number, maybe go a step further depending on which patterns these SpawnID can have. This given value can for example be stored as 2 int4 or one int8: ('428842195.338828'::numeric * 100)::int8 On the other hand this will only addre

[GENERAL] Way to identify the current session's temp tables within pg_class ?

2014-08-25 Thread Marc Mamin
Hello, When different sessions create temp tables with the same name: How can I identify the oid of the one created within the current session ? Thanks, Marc Mamin

Re: [GENERAL] Way to identify the current session's temp tables within pg_class ?

2014-08-25 Thread Marc Mamin
> -Original Message- > From: Michael Paquier [mailto:michael.paqu...@gmail.com] > On Mon, Aug 25, 2014 at 7:48 PM, Marc Mamin > wrote: > > Hello, > > > > When different sessions create temp tables with the same name: > > How can I identify the oid o

Re: [GENERAL] faster way to calculate top "tags" for a "resource" based on a column

2014-10-07 Thread Marc Mamin
ou may check how up to date your statistics are and try to raise the statistic target on the column resource_2_tag.tag_id. Also try a CTE form for your query: WITH A as (SELECT DISTINCT id FROM resource WHERE resource_attribute1_id = 614 ) SELECT resource_2_tag.tag_id AS resource_2_tag_tag_id, count(resource_2_tag.tag_id) AS counted FROM resource_2_tag JOIN A ON A.id = resource_2_tag.resource_id ORDER BY counted DESC LIMIT 25; regards, Marc Mamin -- 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] Removing duplicate records from a bulk upload (rationale behind selecting a method)

2014-12-12 Thread Marc Mamin
ld use a shared full table scan on oldtable. HTH Marc Mamin > >Best regards, >Daniel > >-Original Message- >From: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane >Sent: December-08-14 21:52 >To: Scott Mar

Re: [GENERAL] Removing duplicate records from a bulk upload (rationale behind selecting a method)

2014-12-12 Thread Marc Mamin
the end I'm not sure if multiple threads will help here. I'm using this approach in aggregations which are more cpu intensive than a simple distinct. I'm looking forward to see your tests results :) Marc Mamin > >Daniel > >-Original Message- >

Re: [GENERAL] Combining two queries

2014-12-19 Thread Marc Mamin
HAVING COUNT (*) =2 OR COUNT(case when friend_id =USER1 then true end)=1 ) q1 regards, Marc Mamin

Re: [GENERAL] Combining two queries

2014-12-19 Thread Marc Mamin
end)=1 as are_friend, count(case when c1=2 then true end) as common_friends FROM ( SELECT count(*) as c1 FROM friends WHERE user_id IN (USER1, USER2) GROUP BY case when user_id = USER2 then USER1 else USER1 end, friend_id HAVING COUNT (*) =2 OR COUNT(case when friend_id =USER1 then true end)=1 ) q1 regards, Marc Mamin

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

2015-02-06 Thread Marc Mamin
>Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]" >im Auftrag von "David Johnston [david.g.johns...@gmail.com] >Gesendet: Freitag, 6. Februar 2015 00:38 >An: Tim Smith >Cc: Adrian Klaver; pgsql-general >Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ? >On Thu,

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

2015-02-09 Thread Marc Mamin
rying on the local time, the planner will consider all partitions, but an additional index or constraint on this column should be sufficient as long as your partition count remains small. regards, Marc Mamin

Re: [GENERAL] how to convert "output deleted/inserted into" in My SQL to Postgres

2015-02-21 Thread Marc Mamin
ed >knowledge about Postgres, I don't think we have those in pg. Can someone >tell me how to convert it? Thanks. Hi, it seems that you are looking for UPDATE RETURNING: http://www.postgresql.org/docs/9.4/static/sql-update.html regards, Marc Mamin -- Sent via pgsql-general mailing list

Re: [GENERAL] Postgres architecture for multiple instances

2015-02-22 Thread Marc Mamin
being that users are less isolated (everybody can read the catalog). regards, Marc Mamin -- 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] range type expression syntax

2015-02-26 Thread Marc Mamin
> postgres=# select concat('[', now()::date, ',', now()::date, > ']')::daterange testrange; There are range specific functions for this: select daterange(now()::date, now()::date, '[]') regards, Marc Mamin __

[GENERAL] query syntax to combine 2 set returning functions ?

2013-03-01 Thread Marc Mamin
pends - (TR,_efm_sequences,_efm_sequences_tr) (TR,_emr_reportlist,_emr_reportlist_tr) (TR,_efm_models,_efm_models_del_tr) regards, Marc Mamin

Re: [GENERAL] query syntax to combine 2 set returning functions ?

2013-03-03 Thread Marc Mamin
pg_logs', t)).* FROM get_modeltablelist('efeeds') t ^ ^ Marc From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Marc Mamin Sent: Freitag, 1. März 2013 13:51 To: pgsql-general@postgresql.org Subject: [

[GENERAL] UTC houroffset -> days_start AT TIME ZONE x

2013-06-17 Thread Marc Mamin
time zone. The function below works, but is slow. Any way to build an equivalent function with better performances ? Thanks, Marc Mamin CREATE FUNCTION houroffset_to_daystart (p_houroffset int, p_tz varchar) returns int AS $$ DECLARE daystart int; BEGIN EXECUTE 'select EXTRACT ('

[GENERAL] incomplete CTE declaration and "column reference x is ambiguous"

2013-07-01 Thread Marc Mamin
2 as a, 3 as b ) select * from t1 WHERE b =0 ERROR: column reference "b" is ambiguous. It would be nice, if extra undeclared columns would not be visible outside the CTE. regards, Marc Mamin

[GENERAL] Text search without dictionary ?

2009-06-09 Thread Marc Mamin
n as I don't need it... Maybe someone can also point me to some documentation about writing my own dummy dictionnary? (I'm currently experimenting with 8.3.6, but we will probably implement such functionalities only with 8.4) Thanks, Marc Mamin -- Sent via pgsql-general mailing lis

Re: [GENERAL] Sum of multiplied deltas

2009-06-09 Thread Marc Mamin
Hello, I've found a dirty hack with custom GUC variables here: http://archives.postgresql.org/pgsql-hackers/2008-11/msg00643.php Although dirty, it seems that it beats the windowing performances of 8.4. So I wonder if there are any concern about this... Cheers, Marc Mamin -- Sen

[GENERAL] FILLFACTOR for GIN indexes in 8.3.7

2009-08-14 Thread Marc Mamin
Hello, it seems that I should reduce the Fill Factor of some FTS indexes, but what is the default ? The doc days: > The other index methods use fillfactor in different but roughly analogous ways; > the default fillfactor varies between methods Thanks, Marc Mamin

[GENERAL] Monitoring the sequence cycles

2009-08-19 Thread Marc Mamin
nds, thought I guess this is feature request... best regards, Marc Mamin

Re: [GENERAL] Monitoring the sequence cycles

2009-08-19 Thread Marc Mamin
> How about disabling cycling and doing your own monitoring? > It seems a bit fiddly because sequence values are created "outside" > of any transaction and hence if you write something into a logging > table you're going to loose it if the transaction rolls back. The sequences are expected to

Re: [GENERAL] Hash Support Function

2013-09-08 Thread Marc Mamin
port function >would be sufficient for me. Hello, Have a look at pghashlib: https://github.com/markokr/pghashlib regards, Marc Mamin

Re: [GENERAL] Sum of columns

2013-09-09 Thread Marc Mamin
hi, in addition to the others comments, you can also remove " ELSE 0 " from your query. It will result in values that are discarded by SUM. regards, Marc Mamin From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of jane...@web.de Sent:

Re: [GENERAL] Call for design: PostgreSQL mugs

2013-09-09 Thread Marc Mamin
Hi, "this is not an elephant" (to fake Magritt) http://enculturation.gmu.edu/3_2/images/magritte1.jpg or an elephant breast-feeding its babies to illustrate replication. but better ask someone else for the painting ;-) regards, Marc Mamin > -Original Message- > Fro

  1   2   >