Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Feb 17, 2017 06:53, "John R Pierce"  wrote:

On 2/16/2017 9:43 PM, Magnus Hagander wrote:


>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


he's using HEADER, so cat wouldn't work.he's also using MSDOS/WIndows
style filenames, so cat won't work, anyways..


There are windows equivalents of cat.. But you're right, I didn't think of
the header part.

I'd suggest using something like pgloader, but I don't know if that runs on
MS Windows, either.   its very powerful for doing bulk imports from a wide
range of formats, and even allows data manipulation.


I assume it does if you can figure out how to build it. But I don't think
there are any packages provided for it, so it can be a bit of a challenge.


/Magnus

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[John R Pierce]

On 2/16/2017 9:43 PM, Magnus Hagander wrote:




Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would 
work for you?


he's using HEADER, so cat wouldn't work.he's also using 
MSDOS/WIndows style filenames, so cat won't work, anyways..


I'd suggest using something like pgloader, but I don't know if that runs 
on MS Windows, either.   its very powerful for doing bulk imports from a 
wide range of formats, and even allows data manipulation.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Fri, Feb 17, 2017 at 6:26 AM, Murtuza Zabuawala <
murtuza.zabuaw...@enterprisedb.com> wrote:

> Hi,
>
> Is there any way to load multiple CSV files at once using single COPY
> command?
>
> I have scenario where I have to load multiple files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there any
> other way to do this using single COPY command?
>
>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

[GENERAL] Load multiple CSV file in Postgres using COPY

[Murtuza Zabuawala]

Hi,

Is there any way to load multiple CSV files at once using single COPY
command?

I have scenario where I have to load multiple files,

COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
..
..
..
..
COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y

50 files -> 50 COPY command, In my use case I think this is not a good way
to load data, Can you suggest any better way to do this?

I can always write external script (eg: shell script) but is there any
other way to do this using single COPY command?

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] PostgreSQL corruption

[John R Pierce]

On 2/16/2017 6:48 PM, James Sewell wrote:
Sadly this is for a customer who has 3000 of these in the field, the 
raid controller is on the motherboard. 


if thats the usual Intel "Matrix" raid, thats not  actually a raid 
controller.  its intel sata in fake raid mode, the raid is entirely done 
in host software.




--
john r pierce, recycling bits in santa cruz



--
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] PostgreSQL corruption

[James Sewell]

Sadly this is for a customer who has 3000 of these in the field, the raid
controller is on the motherboard.

At least they know where to point the finger now!

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Fri, Feb 17, 2017 at 1:25 AM, Merlin Moncure  wrote:

> On Tue, Feb 14, 2017 at 7:23 PM, James Sewell 
> wrote:
>
>> OK,
>>
>> So with some help from the IRC channel (thanks macdice and JanniCash)
>>  it's come to light that my RAID1 comprised of 2 * 7200RPM disks is
>> reporting ~500 ops/sec in pg_test_fsync.
>>
>> This is higher than the ~120 ops/sec which you would expect from 720RPM
>> disks - therefore something is lying.
>>
>> Breaking up the RAID and re-imaging with JBOD dropped this to 50 ops/sec
>> - another question but still looking like a real result.
>>
>> So in this case it looks like the RAID controller wasn't disabling
>> caching as advertised.
>>
>
>
> yup -- that's the thing.  Performance numbers really tell the whole (or at
> least most-) of the story.  If it's too good to be true, it is.  These
> days, honestly I'd just throw out the raid controller and install some
> intel ssd drives.
>
> merlin
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[GENERAL] updating dup row

[Patrick B]

Hi all,

how can I update a row with newest id from another table if it exists
somewhere else?

Example:

*table test1*

   - id (primary key)
   - id_user_bill
   - clientid

*table test2*

   - item_id
   - userid (there are duplicated rows here)
   - clientid
   - id (primary key)

-- finding the dup records
INSERT INTO test2_results

SELECT

item_id,

userid

count(*) as dup_count,

MAX(id) as recent_id

FROM

test2

GROUP BY

item_id,

userid

HAVING COUNT(*) > 1;


if test1.id_user_bill = test2.id, then
update test1.id_user_bill with test2_results.recent_id

I'm using PG 9.2

Thanks!
Patrick.

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

[David Hinkle]

I guess this doesn't work, latest test run crashed.  It still uses the
bad plan for the hostid column even after n_distinct is updated.

cipafilter=# select attname, n_distinct from pg_stats where tablename
cipafilter-# = 'log_raw' and (attname = 'urlid' or attname = 'titleid'
or attname =
cipafilter(# 'hostid');
 attname | n_distinct
-+-
 urlid   | 1.51625e+08
 hostid  |  304710
 titleid |  886499
(3 rows)

cipafilter=# explain DELETE FROM hosts WHERE NOT EXISTS ( SELECT
log_raw.hostid FROM log_raw WHERE log_raw.hostid = hosts.hostid );
   QUERY PLAN
-
 Delete on hosts  (cost=22249475.67..74844813.47 rows=1 width=12)
   ->  Hash Anti Join  (cost=22249475.67..74844813.47 rows=1 width=12)
 Hash Cond: (hosts.hostid = log_raw.hostid)
 ->  Seq Scan on hosts  (cost=0.00..5017.10 rows=304710 width=10)
 ->  Hash  (cost=12799395.52..12799395.52 rows=543645052 width=10)
   ->  Seq Scan on log_raw  (cost=0.00..12799395.52
rows=543645052 width=10)
(6 rows)

I guess I will also try throwing in 'set enable_hashjoin = false;' and
see if that gets these purges to go.

On Thu, Feb 16, 2017 at 2:22 PM, David Hinkle  wrote:
> Yep, 420ish million records out of 540 million records have a titleid
> of 1.   There are about 880,000 other unique values, but most of the
> records are 1.   Of course, n_distinct is only 292.   I'm surprised
> it's not eliminating the duplicates while it builds that hash table.
>
> This is what I'm doing for a work around right now.  Getting
> n_distinct right seems to be preventing the system from breaking.
> It's going to be executed once a week during the weekly maintenance.
> It's setting the n_distinct of each column to the number of rows in
> the associated table.
>
> CREATE OR REPLACE FUNCTION patch_ndistinct(_table varchar, _column
> varchar, _string_table varchar)
>   RETURNS real AS
> $$
>  DECLARE _cnt REAL;
> BEGIN
>  SELECT reltuples INTO _cnt from pg_class where relname = 
> _string_table;
>EXECUTE 'ALTER TABLE ' || _table || ' ALTER COLUMN ' || _column
> || ' SET (n_distinct=' || _cnt || ')';
>RETURN _cnt;
> END
> $$ LANGUAGE plpgsql;
> select patch_ndistinct('log_raw', 'titleid', 'titles');
> select patch_ndistinct('log_raw', 'urlid', 'urls');
> select patch_ndistinct('log_raw', 'hostid', 'hosts');
> ANALYZE log_raw;
>
> On Thu, Feb 16, 2017 at 10:54 AM, Tom Lane  wrote:
>> David Hinkle  writes:
>>> Tom, there are three columns in this table that exhibit the problem,
>>> here is the statistics data after an analyze, and the real data to
>>> compare it to.
>>
>>>  attname | n_distinct |  most_common_freqs
>>
>>>  titleid |292 | {0.767167}
>>
>> Ouch.  That's saying there's some single value of titleid that accounts
>> for more than three-quarters of the entries ... does that square with
>> reality?  That'd certainly explain why a hash join goes nuts.
>>
>> regards, tom lane
>
>
>
> --
> David Hinkle
>
> Senior Software Developer
>
> Phone:  800.243.3729x3000
>
> Email:  hin...@cipafilter.com
>
> Hours:  Mon-Fri   8:00AM-5:00PM (CT)



-- 
David Hinkle

Senior Software Developer

Phone:  800.243.3729x3000

Email:  hin...@cipafilter.com

Hours:  Mon-Fri   8:00AM-5:00PM (CT)


-- 
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] Autovacuum stuck for hours, blocking queries

[Alvaro Herrera]

Tom Lane wrote:

> Also you might want to look into how you got into a situation where
> you have an anti-wraparound vacuum that's taking so long to run.

If there are ALTERs running all the time, regular (non-anti-wraparound)
vacuums would be canceled and never get a chance to run.  Eventually,
autovacuum decides it's had enough and doesn't cancel anymore, so
everyone else gets stuck behind.

-- 
Álvaro Herrerahttps://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] Autovacuum stuck for hours, blocking queries

[Tom Lane]

Tim Bellis  writes:
> Even though this is a read only query, is it also expected to be blocked 
> behind the vacuum? Is there a way of getting indexes for a table which won't 
> be blocked behind a vacuum?

It's not the vacuum that's blocking your read-only queries.  It's the
ALTER TABLE, which needs an exclusive lock in order to alter the table's
schema.  The ALTER is queued waiting for the vacuum to finish, and lesser
lock requests queue up behind it.  We could let the non-exclusive lock
requests go ahead of the ALTER, but that would create a severe risk of the
ALTER *never* getting to run.

I'd kill the ALTER and figure on trying again after the vacuum is done.

Also you might want to look into how you got into a situation where
you have an anti-wraparound vacuum that's taking so long to run.
You didn't do something silly like disable autovacuum did you?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

[David Hinkle]

Yep, 420ish million records out of 540 million records have a titleid
of 1.   There are about 880,000 other unique values, but most of the
records are 1.   Of course, n_distinct is only 292.   I'm surprised
it's not eliminating the duplicates while it builds that hash table.

This is what I'm doing for a work around right now.  Getting
n_distinct right seems to be preventing the system from breaking.
It's going to be executed once a week during the weekly maintenance.
It's setting the n_distinct of each column to the number of rows in
the associated table.

CREATE OR REPLACE FUNCTION patch_ndistinct(_table varchar, _column
varchar, _string_table varchar)
  RETURNS real AS
$$
 DECLARE _cnt REAL;
BEGIN
 SELECT reltuples INTO _cnt from pg_class where relname = _string_table;
   EXECUTE 'ALTER TABLE ' || _table || ' ALTER COLUMN ' || _column
|| ' SET (n_distinct=' || _cnt || ')';
   RETURN _cnt;
END
$$ LANGUAGE plpgsql;
select patch_ndistinct('log_raw', 'titleid', 'titles');
select patch_ndistinct('log_raw', 'urlid', 'urls');
select patch_ndistinct('log_raw', 'hostid', 'hosts');
ANALYZE log_raw;

On Thu, Feb 16, 2017 at 10:54 AM, Tom Lane  wrote:
> David Hinkle  writes:
>> Tom, there are three columns in this table that exhibit the problem,
>> here is the statistics data after an analyze, and the real data to
>> compare it to.
>
>>  attname | n_distinct |  most_common_freqs
>
>>  titleid |292 | {0.767167}
>
> Ouch.  That's saying there's some single value of titleid that accounts
> for more than three-quarters of the entries ... does that square with
> reality?  That'd certainly explain why a hash join goes nuts.
>
> regards, tom lane



-- 
David Hinkle

Senior Software Developer

Phone:  800.243.3729x3000

Email:  hin...@cipafilter.com

Hours:  Mon-Fri   8:00AM-5:00PM (CT)


-- 
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] disk writes within a transaction

[Adrian Klaver]

On 02/16/2017 11:33 AM, 2xlp - ListSubscriptions wrote:

Can someone enlighten me to how postgres handles disk writing?  I've read some 
generic remarks about buffers, but that's about it.

We have a chunk of code that calls Postgres in a less-than-optimal way within a 
transaction block.  I'm wondering where to prioritize fixing it, as the traffic 
on the wire isn't an issue.

Basically the code looks like this:

begin;
update foo set foo.a='1' where foo.bar = 1;
...
update foo set foo.b='2' where foo.bar = 1;
...
update foo set foo.c='3' where foo.bar = 1;
commit;

If the updates are likely to be a memory based operation, consolidating them 
can wait.  If they are likely to hit the disk, I should schedule refactoring 
this code sooner than later.


I would suggest taking a look at:

https://www.postgresql.org/docs/9.6/static/wal-configuration.html



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] disk writes within a transaction

[2xlp - ListSubscriptions]

Can someone enlighten me to how postgres handles disk writing?  I've read some 
generic remarks about buffers, but that's about it.

We have a chunk of code that calls Postgres in a less-than-optimal way within a 
transaction block.  I'm wondering where to prioritize fixing it, as the traffic 
on the wire isn't an issue.

Basically the code looks like this:

begin;
update foo set foo.a='1' where foo.bar = 1;
...
update foo set foo.b='2' where foo.bar = 1;
...
update foo set foo.c='3' where foo.bar = 1;
commit;

If the updates are likely to be a memory based operation, consolidating them 
can wait.  If they are likely to hit the disk, I should schedule refactoring 
this code sooner than later.

-- 
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] application generated an eternal block in the database

[Tom Lane]

"Hu, Patricia"  writes:
> I recently came across an interesting locking/blocking situation in a 
> Postgres database(9.5.4, RDS but that shouldn't matter). The application is 
> java/hibernate/springboot with connection pooling. The developers pushed in 
> some code that seemed to be doing this: 
> Start a transaction, update row1 in table1, then spawn another process to 
> update the same row in the same table (but within the context of this 1st 
> transaction?). The result is that the 2nd process was blocked waiting for the 
> lock on the 1st transaction to complete, but the 1st transaction can't 
> complete either b/c the 2nd update was blocked. It wasn't a deadlock 
> situation - neither was rolled back, just more and more locks lined up for 
> that table, till manual intervention by killing the blocker or blocked pid. 

Actually, if I understand you correctly, it *is* a deadlock, but one that
the database cannot detect because one of the waits-for relationships is
internal to the application.  The database can see that session 2 is
waiting for session 1 to complete and release the tuple lock, but it has
no way to know that on the application side session 1 is waiting for
session 2.  So no error is reported, and everything just sits.

AFAICS, this is simply broken application design.  There's no such thing
as a second connection being able to update a row "within the context"
of a first connection's transaction.

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] application generated an eternal block in the database

[Hu, Patricia]

I recently came across an interesting locking/blocking situation in a Postgres 
database(9.5.4, RDS but that shouldn't matter). The application is 
java/hibernate/springboot with connection pooling. The developers pushed in 
some code that seemed to be doing this: 

Start a transaction, update row1 in table1, then spawn another process to 
update the same row in the same table (but within the context of this 1st 
transaction?). The result is that the 2nd process was blocked waiting for the 
lock on the 1st transaction to complete, but the 1st transaction can't complete 
either b/c the 2nd update was blocked. It wasn't a deadlock situation - neither 
was rolled back, just more and more locks lined up for that table, till manual 
intervention by killing the blocker or blocked pid. 

What I saw in the database when this blocking was happening seems pretty 
standard: the 1st update holds a RowExclusiveLock on the table, there is an 
ExclusiveLock on the tuple of the table, another ExclusiveLock on the 
transactionID of the 1st update, the 2nd update unable to acquire a ShareLock 
on the transactionID (granted=f). 

I am trying to understand how could the application have caused this forever 
blocking.. I wasn't able to reproduce it from the database end: if I have 2 
sessions doing update on a same row in same table, after session 1 
commits/rolls back the blocking is resolved. In psql if 2 updates on the same 
row on the same table within the same transaction, on commit psql keeps the 
value of the 2nd update. The developers couldn't explain thoroughly how the 
code triggered this either.

I'd like to see if anyone has insight/explanation how this could happen beyond 
the database boundary in the application layer. Any input is appreciated!

Thanks,
Patricia


Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.


-- 
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] Service configuration file and password security

[Francisco Olarte]

On Thu, Feb 16, 2017 at 2:57 PM, JP Jacoupy  wrote:
> Can the password be stored in an encrypted way inside a service
> configuration file?

There is not

Passwords are not stored in the ( sometimes shared, world readable )
service configuration file ( pg_sevice), but it the UNREADABLE FOR
OTHERS password file. They need to be stored in a way which libpq and
friends can use, so even if they were encrypted they could be
extracted easily byjust debugging and setting a breakpoint in it.

If you are looking at something like a password store with a master
password at least in unix a user account with a .pgpass file works as
it. You store every password in file readable by a user, .pgpass, and
you use that user login credentials to get access to it.

Francisco Olarte.


-- 
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] Service configuration file and password security

[Adrian Klaver]

On 02/16/2017 05:57 AM, JP Jacoupy wrote:

Hello,

This might seem a pretty novice question but I can't find an answer.

Can the password be stored in an encrypted way inside a service
configuration file?


To be clear you are talking about this, correct?:

https://www.postgresql.org/docs/9.6/static/libpq-pgservice.html

If so then no as I understand it. Assuming you have password 
authentication set up to md5, libpq takes care of doing the md5-hash 
before sending it to the server. You do have the following options:


1) https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html

2) https://www.postgresql.org/docs/9.6/static/libpq-envars.html

PGPASSWORD behaves the same as the password connection parameter. Use of 
this environment variable is not recommended for security reasons, as 
some operating systems allow non-root users to see process environment 
variables via ps; instead consider using the ~/.pgpass file (see Section 
32.15).


PGPASSFILE specifies the name of the password file to use for lookups. 
If not set, it defaults to ~/.pgpass (see Section 32.15).


2) And coming in version 10:
http://paquier.xyz/postgresql-2/postgres-10-pgpassfile-connection/



--
Jacoupy Jean-Philippe


Sent from ProtonMail , encrypted email based in
Switzerland.





--
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] Autovacuum stuck for hours, blocking queries

[Adrian Klaver]

On 02/16/2017 08:45 AM, Tim Bellis wrote:

Thank you all - that's really useful :-)

The other query that gets blocked behind the vacuum is the below (truncated).

This query is generated by jdbc in this method:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023)


Is JDBC doing anything else before issuing this?



Even though this is a read only query, is it also expected to be blocked behind 
the vacuum? Is there a way of getting indexes for a table which won't be 
blocked behind a vacuum?


Table 13.2 here:

https://www.postgresql.org/docs/9.5/static/explicit-locking.html

shows the conflicts with SHARE UPDATE EXCLUSIVE(vacuum).

pg_locks:

https://www.postgresql.org/docs/9.5/static/view-pg-locks.html

shows locks being held. So next time it happens I would take a look and 
see if you can work backwards from there.


You could directly access the index information using:

https://www.postgresql.org/docs/9.6/static/catalog-pg-index.html
https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html



Thank you all again,

Tim

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, NOT 
i.indisunique AS NON_UNIQUE,   NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME,   
CASE i.indisclustered WHEN true THEN 1ELSE CASE am.amname   WHEN 'hash' 
THEN 2  ELSE 3END   END AS TYPE,   (i.keys).n AS ORDINAL_POSITION,   
pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME,   CASE 
am.amcanorder WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1   
WHEN 1 THEN 'D'   ELSE 'A' END ELSE NULL   END AS ASC_OR_DESC,   
ci.reltuples AS CARDINALITY,   ci.relpages AS PAGES,   
pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM 
pg_catalog.pg_class ct   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = 
n.oid)   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,   
i.indisunique, i.indisclustered, i.indpred,   i.indexprs,   
information_schema._pg_expandarray(i.indkey) AS keys FROM 
pg_catalog.pg_index i) i ON (ct.oid = i.ind


This query is cut off so cannot say whether it is the issue or not.






--
Adrian Klaver
adrian.kla...@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Service configuration file and password security

[JP Jacoupy]

Hello,

This might seem a pretty novice question but I can't find an answer.

Can the password be stored in an encrypted way inside a service configuration 
file?

--
Jacoupy Jean-Philippe



Sent from [ProtonMail](https://protonmail.ch), encrypted email based in 
Switzerland.

[GENERAL] Function out there to identify pseudo-empty fields, e.g. "n/a", "--", etc?

[Peter Devoy]

Hi all

Just wondering if anyone has come across a function which can identify
typical user-inputted placeholder values?

English language is my scope so typical examples would be "not
applicable", "n/a", "na", "none", "--", etc.

I know it would be trivial to create but no sense in reinventing the wheel.

Kind regards


Peter


-- 
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] Bad planning data resulting in OOM killing of postgres

[Tom Lane]

David Hinkle  writes:
> Tom, there are three columns in this table that exhibit the problem,
> here is the statistics data after an analyze, and the real data to
> compare it to.

>  attname | n_distinct |  most_common_freqs

>  titleid |292 | {0.767167}

Ouch.  That's saying there's some single value of titleid that accounts
for more than three-quarters of the entries ... does that square with
reality?  That'd certainly explain why a hash join goes nuts.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

[Tim Bellis]

Thank you all - that's really useful :-)

The other query that gets blocked behind the vacuum is the below (truncated).

This query is generated by jdbc in this method:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023)

Even though this is a read only query, is it also expected to be blocked behind 
the vacuum? Is there a way of getting indexes for a table which won't be 
blocked behind a vacuum?

Thank you all again,

Tim

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, 
NOT i.indisunique AS NON_UNIQUE,   NULL AS INDEX_QUALIFIER, ci.relname AS 
INDEX_NAME,   CASE i.indisclustered WHEN true THEN 1ELSE CASE am.amname 
  WHEN 'hash' THEN 2  ELSE 3END   END AS TYPE,   (i.keys).n AS 
ORDINAL_POSITION,   pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS 
COLUMN_NAME,   CASE am.amcanorder WHEN true THEN CASE 
i.indoption[(i.keys).n - 1] & 1   WHEN 1 THEN 'D'   ELSE 'A' END
 ELSE NULL   END AS ASC_OR_DESC,   ci.reltuples AS CARDINALITY,   ci.relpages 
AS PAGES,   pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION 
FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_namespace n ON 
(ct.relnamespace = n.oid)   JOIN (SELECT i.indexrelid, i.indrelid, i.indoption, 
  i.indisunique, i.indisclustered, i.indpred,   i.indexprs, 
  information_schema._pg_expandarray(i.indkey) AS keys FROM 
pg_catalog.pg_index i) i ON (ct.oid = i.ind


-- 
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] postgresql how to duplicate rows in result.

[David G. Johnston]

On Thu, Feb 16, 2017 at 9:16 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> For my enlightenment, why use LATERAL here? I get the same result with a
> simple CROSS JOIN (though overall I like the clever solution).
>
>
​To be explicit, I think.  CROSS JOIN function() implies lateral in the
implementation but you can always specify it if desired.  Like saying LEFT
OUTER JOIN instead of LEFT JOIN

If it wasn't lateral the reference to number in "generate_series(1,
number)" would fail.

David J.
​

Re: [GENERAL] postgresql how to duplicate rows in result.

[Steve Crawford]

For my enlightenment, why use LATERAL here? I get the same result with a
simple CROSS JOIN (though overall I like the clever solution).

Cheers,
Steve


On Thu, Feb 16, 2017 at 12:11 AM, Alessandro Baggi <
alessandro.ba...@gmail.com> wrote:

> Il 15/02/2017 19:11, Alessandro Baggi ha scritto:
>
>> Il 14/02/2017 21:51, Merlin Moncure ha scritto:
>>
>>> On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure 
>>> wrote:
>>>
 On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
  wrote:

> Hi list,
> sorry for my english, I will try to example as well. I've a query
> that joins
> multiple tables and return a result like:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
>
> I need, where "number" field is > 1, to duplicate the row * N(number
> field
> value) with a result like this:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?
>

 SELECT * FROM foo CROSS JOIN LATERAL (1,number);

 :-D

>>>
>>> oops -- copy/paste error
>>>
>>> SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);
>>>
>>> merlin
>>> .
>>>
>>> Hi Merlin,
>> I've tried your suggested code and with cross join and generate_series I
>> can generate multiple row. There is a way to put as second args a column
>> values? I've tried to put "table.number" column values but I got
>> "generate_series() does not exists". Inserting a simple int like 5 I get
>> 5 results for each row.
>>
>> I've searched on google but can't find a valid example.
>>
>>
>>
>> Thanks in advance.
>>
>>
>> Hi Merlin,
> I've solved my problem (passing column as number) using a cast
> generate_series(1,table.number::int)
>
> thanks to all for answart.
>
> SOLVED
>
>
> --
> 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 ctid in delete statement

[David G. Johnston]

On Thu, Feb 16, 2017 at 7:52 AM, pinker  wrote:

> Adrian Klaver-4 wrote
> > https://www.postgresql.org/docs/9.6/static/sql-truncate.html
> >
> > https://www.postgresql.org/docs/9.6/static/sql-delete.html
>
> There is nothing about FOR UPDATE clause on those pages...
>

​Both truncate and delete are commands the inherently update tables - there
is no need to have a separate FOR UPDATE clause.

Dave
​

Re: [GENERAL] Using ctid in delete statement

[Adrian Klaver]

On 02/16/2017 07:42 AM, pinker wrote:

Adrian Klaver-4 wrote

Exactly, they do not have it whereas:

https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE


Still not much. The documentation could be more verbose on this topic. I can
only presume that since there is an example with select:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
it's propably possible, but there is no information when the lock is
released (on commit like in oracle?) especially if there is no explicit
BEGIN/END clause like in this case.


From above section:

For more information on each row-level lock mode, refer to Section 13.3.2.

which takes you to:

https://www.postgresql.org/docs/9.6/static/explicit-locking.html#LOCKING-ROWS
"
FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be 
locked as though for update. This prevents them from being locked, 
modified or deleted by other transactions until the current transaction 
ends. That is, other transactions that attempt UPDATE, DELETE, SELECT 
FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY 
SHARE of these rows will be blocked until the current transaction ends; 
conversely, SELECT FOR UPDATE will wait for a concurrent transaction 
that has run any of those commands on the same row, and will then lock 
and return the updated row (or no row, if the row was deleted). Within a 
REPEATABLE READ or SERIALIZABLE transaction, however, an error will be 
thrown if a row to be locked has changed since the transaction started. 
For further discussion see Section 13.4.


The FOR UPDATE lock mode is also acquired by any DELETE on a row, 
and also by an UPDATE that modifies the values on certain columns. 
Currently, the set of columns considered for the UPDATE case are those 
that have a unique index on them that can be used in a foreign key (so 
partial indexes and expressional indexes are not considered), but this 
may change in the future.

"

Which has:

"For further discussion see Section 13.4.":

https://www.postgresql.org/docs/9.6/static/applevel-consistency.html

And from there links to more information.





Oracle documentation is much more clear about it:
You can also use SELECT FOR UPDATE to lock rows that you do not want to
update, as in Example 9-6.








--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944733.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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 ctid in delete statement

[pinker]

Adrian Klaver-4 wrote
> Exactly, they do not have it whereas:
> 
> https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE

Still not much. The documentation could be more verbose on this topic. I can
only presume that since there is an example with select:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
it's propably possible, but there is no information when the lock is
released (on commit like in oracle?) especially if there is no explicit
BEGIN/END clause like in this case.


Oracle documentation is much more clear about it:
You can also use SELECT FOR UPDATE to lock rows that you do not want to
update, as in Example 9-6.
  







--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944733.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] Using ctid in delete statement

[Adrian Klaver]

On 02/16/2017 06:52 AM, pinker wrote:
> Adrian Klaver-4 wrote
>> https://www.postgresql.org/docs/9.6/static/sql-truncate.html
>>
>> https://www.postgresql.org/docs/9.6/static/sql-delete.html
> 
> There is nothing about FOR UPDATE clause on those pages...

Exactly, they do not have it whereas:

https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE


-- 
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 ctid in delete statement

[pinker]

Adrian Klaver-4 wrote
> https://www.postgresql.org/docs/9.6/static/sql-truncate.html
> 
> https://www.postgresql.org/docs/9.6/static/sql-delete.html

There is nothing about FOR UPDATE clause on those pages...




--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944720.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] Using ctid in delete statement

[Adrian Klaver]

On 02/16/2017 02:04 AM, pinker wrote:

Thank you Tom for clarification.
Does it mean that FOR UPDATE clause works with other operations as well?
i.e. TRUNCATE, DELETE?


https://www.postgresql.org/docs/9.6/static/sql-truncate.html

https://www.postgresql.org/docs/9.6/static/sql-delete.html





--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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] Alternate way of xpath

[Adrian Klaver]

On 02/16/2017 04:33 AM, dhaval jaiswal wrote:

I have the following situation.

PostgreSQL is  not configured with the option   --with-libxml

Having one text column where i am trying to run the following command
which is failing as expected its not configure with libxml. However, is
there any alternate way through which i can achieve this.

select xpath('///Name/text()', column1_xml::xml) from test;


Pull the text out and use another language to process:

https://en.wikipedia.org/wiki/XPath#Implementations

either externally or in a Postgres pl* function.



ERROR:  unsupported XML feature
DETAIL:  This functionality requires the server to be built with libxml
support.
HINT:  You need to rebuild PostgreSQL using --with-libxml.



Sent from Outlook 




--
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] PostgreSQL corruption

[Merlin Moncure]

On Tue, Feb 14, 2017 at 7:23 PM, James Sewell 
wrote:

> OK,
>
> So with some help from the IRC channel (thanks macdice and JanniCash)
>  it's come to light that my RAID1 comprised of 2 * 7200RPM disks is
> reporting ~500 ops/sec in pg_test_fsync.
>
> This is higher than the ~120 ops/sec which you would expect from 720RPM
> disks - therefore something is lying.
>
> Breaking up the RAID and re-imaging with JBOD dropped this to 50 ops/sec -
> another question but still looking like a real result.
>
> So in this case it looks like the RAID controller wasn't disabling caching
> as advertised.
>


yup -- that's the thing.  Performance numbers really tell the whole (or at
least most-) of the story.  If it's too good to be true, it is.  These
days, honestly I'd just throw out the raid controller and install some
intel ssd drives.

merlin

[GENERAL] Alternate way of xpath

[dhaval jaiswal]

I have the following situation.

PostgreSQL is  not configured with the option   --with-libxml

Having one text column where i am trying to run the following command which is 
failing as expected its not configure with libxml. However, is there any 
alternate way through which i can achieve this.

select xpath('///Name/text()', column1_xml::xml) from test;

ERROR:  unsupported XML feature
DETAIL:  This functionality requires the server to be built with libxml support.
HINT:  You need to rebuild PostgreSQL using --with-libxml.




Sent from Outlook

Re: [GENERAL] Query with type char

[Christoph Moench-Tegeder]

## Egon Frerich (e...@frerich.eu):

> Why are there 0 rows? I expect 3 rows:

>From your results I guess that sp3 IS NULL on the three rows where
it is not 'j'. You should use the correct comparison predicate for
this case, i.e. IS DISTINCT FROM instead of <>.
https://www.postgresql.org/docs/9.6/static/functions-comparison.html

Regards,
Christoph

-- 
Spare Space.


-- 
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 with type char

[Egon Frerich]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Why are there 0 rows? I expect 3 rows:

> mydb=# SELECT * FROM sample_char;
>  sp1 |   sp2   | sp3
> -+-+-
>1 | Bremen  |
>2 | Hamburg |
>4 | Toronto |
>3 | Bern| j
> (4 rows)
>
> mydb=# SELECT * FROM sample_char WHERE sp3 = 'j';
>  sp1 |   sp2   | sp3
> -+-+-
>3 | Bern| j
> (1 row)
>
> mydb=# SELECT * FROM sample_char WHERE sp3 <> 'j';
>  sp1 | sp2 | sp3
> -+-+-
> (0 rows)

Egon


-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJYpXtvAAoJECc7nbY+bg5uu38P/jXtMHU6AeNwNY5X+B3B9XnD
Wy6AChGzxl1z3pQLpKLrIHPI+y0FFx68vV6TBxpzW/wvpYE7rTM2UfLFyUmcflim
nEhQPUgK/YW3A5yKbjlRQasaosiBrvTvqlP8dSyhIASC1Jxwa152xaTrYOw9+ANO
Z/prP4X1GF7m29P0ULFxthpJlA3FOgtueKcwCVkDQVb2eRISFX8pzhuWKHFHukjx
avMhTo7s0n2unZGmtqrC1m59MR5WYoxbc7Dmwg82/jLqEf4ZYCc4tniqiysl3Tzc
i55xH/aqzVAXhC9PHCxoTU8ey2vyGogRObHo5tCqUpMPNoz0zbeSFrmb829fi3xV
XOKkerx2xXOnxkZkYfCH/a50eWdt9+Nw/ktuHU7G2/UIJQH3DxPi91nuY6trpO5e
nOSqi64F9gm12trCe14Jk8z/Ea7NZCCL5GNrQKXj4zkJF1AagG+bzsefZRz/fkK3
m9Nc2AS9J27mk0jx2SuCc5hHx6o8IdsemZacAamjFa/TYvOau+c6fbHKVvU79uhE
Wxdchse2Zo0/wC6o8nlQqTKmX4xfYKA5Oqk6gmOXC5fqLNOzIYb04IIF6zZAw9Sk
CELmOHyP0ZVyQhAVipqrepgbWhIrRVZV0+a+h5IO/s4QoenFjNV+pKnnwj9BCjw7
duk+MVwoHi6fZk6z601b
=LHt3
-END PGP SIGNATURE-



-- 
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 ctid in delete statement

[pinker]

Thank you Tom for clarification.
Does it mean that FOR UPDATE clause works with other operations as well?
i.e. TRUNCATE, DELETE?



--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.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] postgresql how to duplicate rows in result.

[Alessandro Baggi]

Il 15/02/2017 19:11, Alessandro Baggi ha scritto:

Il 14/02/2017 21:51, Merlin Moncure ha scritto:

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure 
wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
 wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query
that joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1


I need, where "number" field is > 1, to duplicate the row * N(number
field
value) with a result like this:

id,customers,phone,code,number
1 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1

How I can accomplish to this problem?


SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D


oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

merlin
.


Hi Merlin,
I've tried your suggested code and with cross join and generate_series I
can generate multiple row. There is a way to put as second args a column
values? I've tried to put "table.number" column values but I got
"generate_series() does not exists". Inserting a simple int like 5 I get
5 results for each row.

I've searched on google but can't find a valid example.



Thanks in advance.



Hi Merlin,
I've solved my problem (passing column as number) using a cast 
generate_series(1,table.number::int)


thanks to all for answart.

SOLVED


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general