date:20170703

Re: [GENERAL] Accessing DB2 tables from postgresql

2017-07-03 Thread Swapnil Vaze

Hello,

Thanks for help!!

I am following up on that existing thread.

Thanks,
Swapnil Vaze

On Wed, Jun 28, 2017 at 7:01 PM, Adrian Klaver 
wrote:

> On 06/28/2017 01:28 AM, Swapnil Vaze wrote:
>
>> Hello,
>>
>> I dropped and recreated foreign table with odbc_database option. Also
>> tried to use import foreign schema object still getting same error.
>>
>> CREATE FOREIGN TABLE
>>odbc_table (
>> CTGRY_ID INTEGER,
>> CTGRY_CD VARCHAR(10),
>> UPDT_TS TIMESTAMP
>>)
>>SERVER odbc_server
>>OPTIONS (
>>  odbc_database 'TESTV9',
>>  schema 'u90nmqd',
>>  sql_query 'select CTGRY_ID,CTGRY_CD,UPDT_TS from `schema1`.`table1`',
>>  sql_count 'select count(CTGRY_ID) from `schema1`.`table1`'
>>);
>>
>> I have created server with below definition:
>> CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn
>> 'TESTV9');
>>
>
> So do you have a DSN named 'TESTV9' on the system you are connecting from?
>
> If not you will need to use a driver name instead of DSN.
>
>
>> To test for other DBMS, I created foreign object for postgres with
>> odbc_fdw, however it is throwing too long encoding error.
>>
>
> What is the exact error mesage?
>
>
>> Does ODBC wrapper support DB2 access?
>>
>
> That would seem to depend on whether you have a DB2 ODBC driver present on
> your machine:
>
> https://github.com/CartoDB/odbc_fdw
>
> "To make use of the extension ODBC drivers for the data sources to be used
> must be installed in the system and reflected in the /etc/odbcinst.ini
> file."
>
> See also:
>
> https://github.com/CartoDB/odbc_fdw/issues/45
>
> I would ask there, by either responding to existing issue or starting a
> new issue.
>
>
>
>> Thanks,
>> Swapnil Vaze
>>
>>
>> On Tue, Jun 27, 2017 at 6:38 PM, Adrian Klaver > > wrote:
>>
>> On 06/27/2017 03:11 AM, Swapnil Vaze wrote:
>>
>> Hello,
>>
>> I am trying to access few table present in DB2 LUW from
>> postgres9.5 database.
>>
>> I have installed unixODBC driver and connection to DB2 is
>> working fine.
>> I have installed CartoDB/odbc_fdw foreign data wrappers.
>> I have user below commands to create foreign table:
>> $ create extension odbc_fdw;
>> $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2
>> options(odbc_UID '', odbc_PWD '');
>> $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD
>> VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS
>> (database 'TESTV9', schema 'schema1', table 'table1' );
>>
>> All commands work fine, however when I try to select data from
>> table it throws error:
>> $ select * from odbc_table;
>> ERROR:  Executing ODBC query
>>
>> Can anyone help me here?
>>
>>
>> Might want to take a look at:
>>
>> https://github.com/CartoDB/odbc_fdw
>> 
>>
>> From  the examples above you need to prefix some settings with
>> odbc_. In your example that would be odbc_database 'TESTV9' instead
>> of database 'TESTV9'.
>>
>>
>> How can I access DB2 LUW or zOS database tables from postgres?
>>
>> -- Thanks & Regards,
>> Swapnil Vaze
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>>
>> --
>> Thanks & Regards,
>> Swapnil Vaze
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Thanks & Regards,
Swapnil Vaze

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-03 Thread rajan

Thanks for the explanation.

will I be able to view the information using this function,
SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0));

Also, please let me know which column I should refer for viewing the
pointer.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969767.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] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-03 Thread Jeff Janes

On Mon, Jul 3, 2017 at 3:02 AM, rajan  wrote:

> Thanks for the explanation.
>
> will I be able to view the information using this function,
> SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0));
>
> Also, please let me know which column I should refer for viewing the
> pointer.
>


It is 't_ctid'

Cheers,

Jeff

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-03 Thread rajan

Thanks, Jeff.

Now I am going back to my old question.

Even though *Session 2* fails to update with UPDATE 0 message, its txid is
saved in xmax of updated(by *Session 1*) tuple.

As it becomes an old txid, how come new txids are able to view it?



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969857.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] EXCEPTION handling from с function

2017-07-03 Thread Арсен Арутюнян

Hello please assist

How i can handle nowait EXCEPTION from c function;

select for update nowait 

SPI_execute_plan(UpdateStat, Values, NULL, false, 1);

Arsen 


-- 
Арсен Арутюнян

[GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

2017-07-03 Thread Jerry Regan

For reasons beyond my control, I am using Postgresql 9.4 on a MacBookPro 
(development system). I use pgadminIII and psql for clients (I tried and didn’t 
like the pgadmin4 UI; pg_dump, pg_restore also seem to be dumbed down).

My question:
I have some performance test results in table cor_duration_report. One column, 
c_entered_ion is of type timestamptz, another, c_scenario, is of type text. I 
want to calculate the difference between succeeding c_entered_ion rows to learn 
the rate at which entry events occur. In cor_duration_report, c_entered_ion 
columns are NOT in ascending sort order.

For a first attempt, I created another table cor_temp_gap as:
CREATE TABLE cor_temp_gap
(
  c_id serial NOT NULL,
  c_entered_ion timestamp with time zone NOT NULL,
  c_scenario text NOT NULL
)
WITH (
  OIDS=FALSE
);

and loaded it with:
INSERT into cor_temp_gap (c_entered_ion, c_scenario) SELECT c_entered_ion, 
c_scenario from cor_duration_report order by c_entered_ion;

The c_id column is loaded with the default value - the next sequence value.

I then generated my report with:
select count( gap ) as gaps, sum(gap) as sum,
   mode() within group (order by gap) as mode,
   percentile_disc(0.5) within group (order by gap) as  median,
   avg( gap::integer ) as mean, 
   
   min( gap ) as min,   
   
   max( gap ) as max 
from ( select extract( epoch from ( f.c_entered_ion - 
s.c_entered_ion)::interval) * 1000 as gap 
   from cor_temp_gap s, cor_temp_gap f 
   where s.c_scenario = '20170628tc04' 
   and s.c_id+1 = f.c_id ) vals;

This seems to give me the results I want:
 gaps |   sum  | mode| median | mean
| min |  max   
--+-+--++---+-+
 307412 | 6872207 |1   |  8  | 22.3550381897908995|   0 | 
10846

The min value of zero is accurate. The mode value of 1 is reasonable, as is the 
median value of 8. Using a totally different method, the mean value is 
accurate, as is gaps (there are 307,413 rows in the table).

I do know enough sql to believe my cor_temp_gap table could probably be replace 
by a ‘WITH SELECT….’

I attempted this:
with cor_entry_time as ( select nextval('cor_temp_select_c_id_seq') as c_id, 
c_entered_ion, c_scenario
from cor_duration_report where c_scenario = '20170628tc04' order by 
c_entered_ion )
select count( gap ) as gaps,
   sum(gap::integer) as sum,
  
   mode() within group (order by gap) as mode,  
   
   percentile_disc(0.5) within group (order by gap) as  median, 
   
   avg( gap::integer ) as mean, 
   
   min( gap::integer ) as min,  

   max( gap::integer ) as max 
from ( select extract( epoch from ( f.c_entered_ion - 
s.c_entered_ion)::interval) * 1000 as gap 
   from cor_entry_time s, cor_entry_time f 
   where s.c_id+1 = f.c_id ) vals;

which returned:

  gaps|   sum  | mode | median |mean   |   
min   |  max  
+-+--++-+--+---
 307412 | 6867802 |0 |461   | 22.3407088857949592   | -6871881 | 
59791

The results should match but obviously they don’t. the ‘mode’, ‘median’, ‘min’ 
and ‘max’ columns are clearly different. The ‘sum’ and ‘mean’ columns are 
similar but are also different. Only ‘gaps’ is the same. There should be no 
negative numbers at all, assuming my c_entered_ion column is in ascending 
order. Wouldn’t the 'order by c_entered_ion’ in the ‘WITH’ select do that?

To me, the differences between loading ‘cor_temp_gaps’ and my ‘WITH’ select 
should not cause the difference. The differences are in how ‘c_id’ is generated 
and the fact that selecting only ‘c_scenario = ‘20170628tc04’ has been moved 
from calculating the interval to the ‘WITH’ select. I have also tried the ‘WITH 
SELECT’ approach without moving that test and received the same results.

My suspicion is that in the failing approach, my sequence is being assigned 
before the sort whereas when I load ‘cor_temp_gap’, and c_id defaults to a 
sequence then c_id is generated AFTER c_entered_ion is put in sort order.

If my suspicion is right, how do accomplish the same thing in the ‘WITH SELECT’ 
case?

If it is wrong, what am I doing wrong?

Thanks for any insights you may be able to provide!

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for o

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-03 Thread Timokhin Maxim

BTW, we are moving using:

pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v 
—xlog-method=stream —checkpoint=fast

After that we are upping version to 9.6.3.
I've looked through the documentation 
https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't find 
details about how pg_basebackup works with b-tree indexes.
Is it possible that pg_basebackup just copies indexes as is and that is cause 
of corruption. Or it pass indexes as instruction that says "after upping db 
make indexes" ? 

Thank you.


-- 
Timokhin 'maf' Maxim



30.06.2017, 00:22, "Melvin Davidson" :
> On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim  wrote:
>> Hello.
>> We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our 
>> application started to throw exception "duplicate key value violates unique 
>> constraint" during doing INSERT:
>>
>> INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, 
>> status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, 
>> checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, 
>> %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, 
>> %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) 
>> RETURNING items.id'
>>
>> Column url has unique constraint.
>>
>> Also, we saw that during to update value into column status:
>> (psycopg2.IntegrityError) duplicate key value violates unique constraint 
>> "items_url"
>>     DETAIL:  Key 
>> (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880)
>>  already exists.
>>      [SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...
>>
>> Our table:
>>
>>      Column      |            Type             |                             
>> Modifiers
>> -+-+---
>>  id              | integer                     | not null default 
>> nextval(('public.items_id_seq'::text)::regclass)
>>  ctime           | timestamp without time zone | not null default now()
>>  pubdate         | timestamp without time zone | not null default now()
>>  resource_id     | integer                     | not null default 0
>>  url             | text                        |
>>  title           | text                        |
>>  description     | text                        |
>>  body            | text                        |
>>  status          | smallint                    | not null default 0
>>  image           | text                        |
>>  orig_id         | integer                     | not null default 0
>>  mtime           | timestamp without time zone | not null default now()
>>  checksum        | text                        |
>>  video_url       | text                        |
>>  audio_url       | text                        |
>>  content_type    | smallint                    | default 0
>>  author          | text                        |
>>  video           | text                        |
>>  fulltext_status | smallint                    | default 0
>>  summary         | text                        |
>>  image_id        | integer                     |
>>  video_id        | integer                     |
>>  priority        | smallint                    |
>> Indexes:
>>     "items_pkey" PRIMARY KEY, btree (id)
>>     "items_url" UNIQUE, btree (url)
>>     "items_resource_id" btree (resource_id)
>>     "ndx__items__ctime" btree (ctime)
>>     "ndx__items__image" btree (image_id)
>>     "ndx__items__mtime" btree (mtime)
>>     "ndx__items__pubdate" btree (pubdate)
>>     "ndx__items__video" btree (video_id)
>> Foreign-key constraints:
>>     "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE 
>> CASCADE ON DELETE SET NULL
>>     "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE 
>> CASCADE ON DELETE SET NULL
>> Referenced by:
>>     TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY 
>> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY 
>> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) 
>> REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY 
>> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) 
>> REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY 
>> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY 
>> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>>     TABLE "items_summaries"

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-03 Thread Timokhin Maxim

Hello! Yes, it looks like a bug or an index corruption. Now, I'm going to drop an index, find and fix duplicates, and create index again.But I would do it on 9.6.3 because there is a great feature ''max_parallel_workers_per_gather" there.Well, see what will happen. -- Пожалуйста!Используйте кнопку "ответить всем".Не удаляйте историю переписки.Спасибо. С уважением, Timokhin 'maf' Maxim   30.06.2017, 00:22, "Melvin Davidson" :  On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim  wrote:Hello.We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT: INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id' Column url has unique constraint.  Also, we saw that during to update value into column status:(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"    DETAIL:  Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880) already exists.     [SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...  Our table:       Column      |            Type             |                             Modifiers-+-+--- id              | integer                     | not null default nextval(('public.items_id_seq'::text)::regclass) ctime           | timestamp without time zone | not null default now() pubdate         | timestamp without time zone | not null default now() resource_id     | integer                     | not null default 0 url             | text                        | title           | text                        | description     | text                        | body            | text                        | status          | smallint                    | not null default 0 image           | text                        | orig_id         | integer                     | not null default 0 mtime           | timestamp without time zone | not null default now() checksum        | text                        | video_url       | text                        | audio_url       | text                        | content_type    | smallint                    | default 0 author          | text                        | video           | text                        | fulltext_status | smallint                    | default 0 summary         | text                        | image_id        | integer                     | video_id        | integer                     | priority        | smallint                    |Indexes:    "items_pkey" PRIMARY KEY, btree (id)    "items_url" UNIQUE, btree (url)    "items_resource_id" btree (resource_id)    "ndx__items__ctime" btree (ctime)    "ndx__items__image" btree (image_id)    "ndx__items__mtime" btree (mtime)    "ndx__items__pubdate" btree (pubdate)    "ndx__items__video" btree (video_id)Foreign-key constraints:    "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL    "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULLReferenced by:    TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE    TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE  Everything would be not bad if in the table weren't appeared duplicated records in url column.An

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-03 Thread Timokhin Maxim

Yes, you are right. It's just a typo. -- Пожалуйста!Используйте кнопку "ответить всем".Не удаляйте историю переписки.Спасибо. С уважением, Timokhin 'maf' Maxim   30.06.2017, 02:38, "Adrian Klaver" :On 06/29/2017 02:28 AM, Timokhin Maxim wrote: Hello. We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:How did move from 9.4.8 --> 9.6.3.1?Also where are you getting Postgres from? I ask because 9.6.3.1 is nota community version or was that just a typo?  Everything would be not bad if in the table weren't appeared duplicated records in url column. Any idea how is it possible? Thank you! -- Timokhin 'maf' Maxim --Adrian Klaveradrian.kla...@aklaver.com

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-03 Thread Timokhin Maxim

Sure, here it is.

pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v 
—xlog-method=stream —checkpoint=fast

/usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8 
—locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8 
—lc-messages=en_US.utf8

Then updating:
/usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d /data/upgrade/94 -B 
/usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k

and so on to 9.6

after that server starts normally.


-- 
Timokhin 'maf' Maxim


30.06.2017, 16:07, "Adrian Klaver" :
> On 06/30/2017 04:58 AM, Timokhin Maxim wrote:
>>  BTW, we are moving using:
>>
>>  pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v 
>> —xlog-method=stream —checkpoint=fast
>
> Going from 9.4 to 9.6 is a major version upgrade and you cannot use
> pg_basebackup for that. Besides I can't see how you even got the 9.6.3
> server to start:
>
> /usr/local/pgsql94/bin/pg_basebackup -D /home/aklaver/pgback_test94 -U
> postgres -p 5412 -v --xlog-method=stream
>
> /usr/local/pgsql96/bin/pg_ctl -D /home/aklaver/pgback_test94/ start
> server starting
> FATAL: database files are incompatible with server
> DETAIL: The data directory was initialized by PostgreSQL version 9.4,
> which is not compatible with this version 9.6.3
>
>>  After that we are upping version to 9.6.3.
>
> Given the above how did you actually get 9.6.3 to start?
>
>>  I've looked through the documentation 
>> https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't find 
>> details about how pg_basebackup works with b-tree indexes.
>>  Is it possible that pg_basebackup just copies indexes as is and that is 
>> cause of corruption. Or it pass indexes as instruction that says "after 
>> upping db make indexes" ?
>>
>>  Thank you.
>>
>>  --
>>  Timokhin 'maf' Maxim
>
> --
> 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] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

2017-07-03 Thread Adrian Klaver


On 07/02/2017 10:33 AM, Jerry Regan wrote:
For reasons beyond my control, I am using Postgresql 9.4 on a MacBookPro 
(development system). I use pgadminIII and psql for clients (I tried and 
didn’t like the pgadmin4 UI; pg_dump, pg_restore also seem to be dumbed 
down).


My question:
I have some performance test results in table cor_duration_report. One 
column, c_entered_ion is of type timestamptz, another, c_scenario, is of 
type text. I want to calculate the difference between succeeding 
c_entered_ion rows to learn the rate at which entry events occur. In 
cor_duration_report, c_entered_ion columns are NOT in ascending sort order.


For a first attempt, I created another table cor_temp_gap as:

CREATE TABLE cor_temp_gap
(
   c_id serial NOT NULL,
   c_entered_ion timestamp with time zone NOT NULL,
   c_scenario text NOT NULL
)
WITH (
   OIDS=FALSE
);

and loaded it with:

INSERT into cor_temp_gap (c_entered_ion, c_scenario) SELECT
c_entered_ion, c_scenario from cor_duration_report order by
c_entered_ion;

The c_id column is loaded with the default value - the next sequence value.

I then generated my report with:

select count( gap ) as gaps, sum(gap) as sum,
mode() within group (order by gap) as mode,
percentile_disc(0.5) within group (order by gap) as  median,
avg( gap::integer ) as mean,
min( gap ) as min,
max( gap ) as max
from ( select extract( epoch from ( f.c_entered_ion -
s.c_entered_ion)::interval) * 1000 as gap
from cor_temp_gap s, cor_temp_gap f
where s.c_scenario = '20170628tc04'
and s.c_id+1 = f.c_id ) vals;


This seems to give me the results I want:

  gaps |   sum  | mode| median | mean   
 | min |  max

--+-+--++---+-+
  307412 | 6872207 |1   |  8  | 22.3550381897908995
|   0 | 10846


The min value of zero is accurate. The mode value of 1 is reasonable, as 
is the median value of 8. Using a totally different method, the mean 
value is accurate, as is gaps (there are 307,413 rows in the table).


I do know enough sql to believe my cor_temp_gap table could probably be 
replace by a ‘WITH SELECT….’


I attempted this:

with cor_entry_time as ( select nextval('cor_temp_select_c_id_seq')
as c_id, c_entered_ion, c_scenario
from cor_duration_report where c_scenario = '20170628tc04' order by
c_entered_ion )
select count( gap ) as gaps,
sum(gap::integer) as sum,
mode() within group (order by gap) as mode,
percentile_disc(0.5) within group (order by gap) as  median,
avg( gap::integer ) as mean,
min( gap::integer ) as min,
max( gap::integer ) as max
from ( select extract( epoch from ( f.c_entered_ion -
s.c_entered_ion)::interval) * 1000 as gap
from cor_entry_time s, cor_entry_time f
where s.c_id+1 = f.c_id ) vals;


I used this site to reformat the above:

http://sqlformat.darold.net/

WITH cor_entry_time AS (
SELECT
nextval('cor_temp_select_c_id_seq') AS c_id,
c_entered_ion,
c_scenario
FROM
cor_duration_report
WHERE
c_scenario = '20170628tc04'
ORDER BY
c_entered_ion
)
SELECT
count(gap) AS gaps,
sum(gap::INTEGER) AS SUM,
MODE ()
WITHIN
GROUP (
ORDER BY
gap) AS MODE,
percentile_disc (0.5)
WITHIN
GROUP (
ORDER BY
gap) AS median,
avg(gap::INTEGER) AS mean,
min(gap::INTEGER) AS MIN,
max(gap::INTEGER) AS MAX
FROM (
SELECT
extract(EPOCH
FROM (f.c_entered_ion - s.c_entered_ion)::interval) * 1000 AS gap
FROM
cor_entry_time s,
cor_entry_time f
WHERE
s.c_id + 1 = f.c_id) vals;


Still have not figured out everything that is going on above, but it 
gave me a fighting chance:)







which returned:

   gaps|   sum  | mode | median |mean  
 |   min   |  max

+-+--++-+--+---
  307412 | 6867802 |0 |461   | 22.3407088857949592   |
-6871881 | 59791

The results should match but obviously they don’t. the ‘mode’, ‘median’, 
‘min’ and ‘max’ columns are clearly different. The ‘sum’ and ‘mean’ 
columns are similar but are also different. Only ‘gaps’ is the same. 
There should be no negative numbers at all, assuming my c_entered_ion 
column is in ascending order. Wouldn’t the 'order by c_entered_ion’ in 
the ‘WITH’ select do that?


I believe you are going to have to move the ORDER BY to outside the 
WITH. If I am following correctly:


FROM (
SELECT
extract(EPOCH
FROM (f.c_entered_ion - s.c_entered_ion)::interval) * 1000 AS gap
FROM
cor_entry_time s,
cor_entry_time f
WHERE
s.c_id + 1 = f.c_id
ORDER BY
s.c__entered_i

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

2017-07-03 Thread Adrian Klaver


On 07/03/2017 05:20 PM, Jerry Regan wrote:

Adrian,

Thank you for your reply!

I apologize in advance for not being detailed below. Hard to do from my phone.

I did have to move the 'ORDER BY', but not outside the 'WITH'. My first 
workaround parenthesized the select containing the 'ORDER BY', forcing it to be 
evaluated before the 'INSERT'. That worked.


Not sure where the INSERT comes into the picture, but glad you got it 
working.




But I never liked using a sequence for the c_id column. And using the sequence 
on my personal workstation was maybe safe, but given that sequences not are 
guaranteed to be without gaps, that was not very portable.


Yeah, that concerned me also, still I figured one problem at a time.



So I searched a bit and found I could use 'row_number()' instead. That approach 
allowed me to use the 'ORDER BY' required by 'row_number()'.

That worked and is far more portable to other postgresql instances.

I really do appreciate your response. It is also my nature to continue my 
research even after asking for help. However I find my answer, one validates 
the other.


Sometimes it just a matter a little push to get out of the rut:)



Thanks again!

/s/jr
Sent from my iPhone



--
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] Accessing DB2 tables from postgresql

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

[GENERAL] EXCEPTION handling from с function

[GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

12 matches

Site Navigation

Mail list logo

Footer information