Re: Converting yes or no to one letter strings.

[Ron]

On 6/4/19 7:19 PM, Adrian Klaver wrote:

On 6/4/19 3:29 PM, Lou wrote:

Hi everyone,

Is it possible to convert a boolean yes or no field to hold a one letter 
string? For example, the strings: 's' 'f' 'p' 'e'


To start off, I just need to convert true to 's'. false will have to be 
manually changed to 'f' or 'p' or 'e' as appropriate.


Why not an enum field?:

https://www.postgresql.org/docs/11/datatype-enum.html


I thought enum was Considered Harmful because of the difficulty in altering 
the enums?


--
Angular momentum makes the world go 'round.

Re: Query very different speeds on seemingly similar data

[David Rowley]

On Wed, 5 Jun 2019 at 04:55, Rob Northcott  wrote:
> Explain files attached (assuming attachments will get through to the group – 
> otherwise what’s the best way to post it?)

You're best to post the EXPLAIN ANALYZE output to
https://explain.depesz.com  what you attached is pretty horrible to
read and details are only gained by hovering the mouse cursor over the
node. I for one struggle to work out what's the inner and outer sides
of the join with the output you've given and can only work it out by
looking at the nested loop plan to see which side the parameter is on.

It appears that the sub-query plan has changed from a Nested Loop plan
to a Merge Join. The Merge Join is a pretty bad plan since the index
that provides the pre-sorted input must filter out many non-matching
rows. It's not quite clear to me why the planner chooses that index,
mostly because I'm too lazy to learn the output you've shown the plans
in, but if you did have an index on sales_invoicedetails (std_stk_key,
std_unique), then the Merge Join plan would likely produce a better
plan, or at least better than the current Merge Join plan.

You'll likely want to ensure that random_page_cost has not been set to
something insane on the Merge Join instance.  I'd also check
seq_page_cost too and also effective_cache_size.

More recommendations might be easier to give if you show the plans in
a better format.

Apart from that, you could consider also instead of performing a
sub-query, LEFT JOIN to a query similar to your subquery but after
removing the std_stk_key = stock.stk_key condition and adding a GROUP
BY std_stk_key. However, that may perform worse if there are many more
std_stk_key groups than there are matching rows in stock.stk_key.
Experimentation might be required there.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Converting yes or no to one letter strings.

[Adrian Klaver]

On 6/4/19 3:29 PM, Lou wrote:

Hi everyone,

Is it possible to convert a boolean yes or no field to hold a one letter 
string? For example, the strings: 's' 'f' 'p' 'e'


To start off, I just need to convert true to 's'. false will have to be 
manually changed to 'f' or 'p' or 'e' as appropriate.


Why not an enum field?:

https://www.postgresql.org/docs/11/datatype-enum.html





Lou




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

Re: Converting yes or no to one letter strings.

[Rich Shepard]

On Tue, 4 Jun 2019, David G. Johnston wrote:


Actually, given that "f" is becoming multiple different values it seems
like the OP is improving upon their data model. That should very much be
done at the table level at not relegated to views, let alone a UI layer.

It is not unusual to want to convert something that was a simple boolean
to an enumeration of values. Though it is probably better to add a new
column to reflect the new model element and effectively deprecate the old
column - eventually removing it from use when it is no longer needed.
Altering the column type, while possible, is not usually desirable. In
particular it makes rolling back (or supporting older) client software
more difficult because the old version will not recognize the new field
and the old field will be gone.


David,

Thanks for the insights.

Rich

Re: Converting yes or no to one letter strings.

[David G. Johnston]

On Tue, Jun 4, 2019 at 4:01 PM Rich Shepard 
wrote:

> On Tue, 4 Jun 2019, Lou wrote:
>
> > To start off, I just need to convert true to 's'. false will have to be
> > manually changed to 'f' or 'p' or 'e' as appropriate.
>
> Second, if you need to display to the user something other than 't' and 'f'
> that should be done with your UI, not by changing postgres.
>

Actually, given that "f" is becoming multiple different values it seems
like the OP is improving upon their data model.  That should very much be
done at the table level at not relegated to views, let alone a UI layer.

It is not unusual to want to convert something that was a simple boolean to
an enumeration of values.  Though it is probably better to add a new column
to reflect the new model element and effectively deprecate the old column -
eventually removing it from use when it is no longer needed.  Altering the
column type, while possible, is not usually desirable.  In particular it
makes rolling back (or supporting older) client software more difficult
because the old version will not recognize the new field and the old field
will be gone.
David J.

Re: Converting yes or no to one letter strings.

[David G. Johnston]

On Tue, Jun 4, 2019 at 3:30 PM Lou  wrote:

> Is it possible to convert a boolean yes or no field to hold a one letter
> string? For example, the strings: 's' 'f' 'p' 'e'
>
Something like the following should work:

ALTER TABLE ... ALTER COLUMN ... TYPE text USING (CASE WHEN ... THEN 's'
ELSE 'f' END);

https://www.postgresql.org/docs/11/sql-altertable.html

Though at this point you are probably better off creating a category table
with a primary key and converting this column to be a foreign key.

David J.

Re: Converting yes or no to one letter strings.

[Ray O'Donnell]

On 4 June 2019 23:30:33 Lou  wrote:

Hi everyone,
Is it possible to convert a boolean yes or no field to hold a one letter 
string? For example, the strings: 's' 'f' 'p' 'e'
To start off, I just need to convert true to 's'. false will have to be 
manually changed to 'f' or 'p' or 'e' as appropriate.

Lou

select case when (boolean variable) then (whatever) else (whatever else) end;

Re: Converting yes or no to one letter strings.

[Rich Shepard]

On Tue, 4 Jun 2019, Lou wrote:


To start off, I just need to convert true to 's'. false will have to be
manually changed to 'f' or 'p' or 'e' as appropriate.


Lou,

I'm far from an expert so take my comments with a bag of salt. First,
'false' is displayed in a column as 'f' when you look at a table with psql.
'true' is displayed as 't'.

Second, if you need to display to the user something other than 't' and 'f'
that should be done with your UI, not by changing postgres.

Regards,

Rich

Converting yes or no to one letter strings.

[Lou]

Hi everyone,

Is it possible to convert a boolean yes or no field to hold a one letter
string? For example, the strings: 's' 'f' 'p' 'e'

To start off, I just need to convert true to 's'. false will have to be
manually changed to 'f' or 'p' or 'e' as appropriate.

Lou

postgresql11-devel RPM is missing from "Direct RPM downloads"

[Dmitry O Litvintsev]

Hi,

Where can I find postgresq11-devel RPM ?

It is missing from direct RPM download page :

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/

or

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html

Thanks,
Dmitry

Re: dump and restore ordered by schema or table name

[Fabrízio de Royes Mello]

Em ter, 4 de jun de 2019 às 17:16, PegoraroF10  escreveu:

> Our database has 180 schemas with 100 tables each, with majority of them
> being small tables. Then, when we do a dump or restore we don´t know how
> much time it´ll spend to do that job.
>
> So, there is an option to dump or restore ordered alphabetically ? It could
> be by schema or table, do we have it ?
>
>
Did you check the -l and -L options? It can help you.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

dump and restore ordered by schema or table name

[PegoraroF10]

Our database has 180 schemas with 100 tables each, with majority of them
being small tables. Then, when we do a dump or restore we don´t know how
much time it´ll spend to do that job.

So, there is an option to dump or restore ordered alphabetically ? It could
be by schema or table, do we have it ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Two small questions re/ COPY CSV data into table

[Francisco Olarte]

Mathias:

On Tue, Jun 4, 2019 at 8:35 PM Matthias Apitz  wrote:
> El día Tuesday, June 04, 2019 a las 05:41:43PM +0200, Francisco Olarte 
> escribió:

> > On Tue, Jun 4, 2019 at 5:03 PM Matthias Apitz  wrote:
> > > Well, it's not strictly CSV, but it is what the Sybase tool produces. The
> > > delimiter is set to '|' and a data example line looks like:
> > > 0|1| cat $1 \| lpr -Pprinter |3|4
> > > I do load this now with COPY in mode TEXT and modify the data before
> > > with:
> > >  sed 's/|/\v/g' < table-from-Sybase  | sed 's/\\\v/|/g' > table-for-copy
> > You are lucky it is not CSV, it is much better, it's similar to the
> > text format originally used by postgres. Your problem is just it uses
> > | instead of TAB for field delimiter.

> > What I d not know is why you use \v, doesn't sed use \t for TAB?
> I was thinking about \t and was afraid, that in ~2 GByte char fields
> even some \t could be used in the data. That's why I decided to use \v
> which is more unlikely to be used in our data.

Fine then. I haven't got much sed-foo under my belt, but if you do
this in perl ( I learned perl a while ago, forgot all awk and sed to
free some brain cells ;-> ) or something similar you can use high code
points or control chars ( and I do not know if sed can do several
replacements in a pass, but perl certainly can ). Something like (
untested ) "perl -pe 's//\001/g; s/\\\|/\002/; s/\|/\t/;
s/\002/|/; s/\001//", the advantage over the pipe approach may be
noticeable on big files, as it cuts a lot of context switches ( OTOH
it'll run single-core ).

( the sample tries to do
scaped backslash to soh
scaped pipe to stx
pipe to tab
stx to unescaped pipe
soh to escaped backlash
)

In fact you can do "tab to backslash t" after phase 2 and tab would be
ok in the input.

But, as I said before and others have also pointed, COPY is perfectly
happy to use pipe as delimiter and havinf it escaped with backslash on
input.

> (You didn't cc'ed the list, by intention?)

No, I forgot to hit reply-all because I did not pay enough attention,
my fault. Doing it now in case some one thinks this is interesting.

Francisco Olarte.

Re: Two small questions re/ COPY CSV data into table

[Andrew Gierth]

> "Matthias" == Matthias Apitz  writes:

 Matthias> it is exported into the file as

 Matthias>  cmd \| lpr ...

 Matthias> because the export delimiter is set to '|';

 Matthias> the COPY in text mode was complaining because it interpreted
 Matthias> the | of the sequence \| as an delimiter and the number of
 Matthias> columns did not matched.

 Matthias> I will provide tomorrow the exact input line, the exact COPY
 Matthias> command and the error.

This worked for me:

postgres=# copy ct(a,b,c,d,e) from stdin with delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 0|1| cat $1 \| lpr -Pprinter |3|4
>> \.
COPY 1

-- 
Andrew (irc:RhodiumToad)

Re: Two small questions re/ COPY CSV data into table

[Matthias Apitz]

El día Tuesday, June 04, 2019 a las 05:56:49PM +0100, Andrew Gierth escribió:

>  Matthias> The new DELIMITER for PG is then vtab and the | is just a char in 
> the
>  Matthias> data (in the example above a pipe of two UNIX cmd).
> 
> Yes, but why?
> 
> If you use COPY ... WITH DELIMITER '|' (i.e. text mode, not CSV mode)
> then the \| is accepted as being a literal | and the unescaped | is
> treated as a delimiter. What is the point of the substitutions?

In the original data record in Sybase there is a column containing, i.e.
some UNIX print command to be read and executed by the application:

cmd | lpr ...

it is exported into the file as

cmd \| lpr ...

because the export delimiter is set to '|';

the COPY in text mode was complaining because it interpreted the |
of the sequence \| as an delimiter and the number of columns did not
matched.

I will provide tomorrow the exact input line, the exact COPY command and
the error.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: psql \copy

[paul.malm]

Thanx, it worked! :)

4 juni 2019 kl. 11:00 skrev Pavel Stehule 
mailto:pavel.steh...@gmail.com>>:

Hi

út 4. 6. 2019 v 10:50 odesílatel mailto:paul.m...@lfv.se>> 
napsal:
Hi. I’m trying to execute this command:
C:\P_bin\psql -h 10.233.37.24 -p 5432 -U tester -d test -w  -c "\copy 
public.BadenWuerttemberg_20181011 FROM C:\data\Baden-Wuerttemberg.csv' WITH 
DELIMITER ';' CSV"
Error:  relation "public.badenwuerttemberg_20181011" does not exist.
What am I Doing wrong?

looks like case sensitive identifier is used. try

"\copy public.""BadenWuerttemberg_20181011"" ...

Regards

Pavel Stehule



There is a table (badenwuerttemberg_20181011) in database test under the public 
schema. The table has all the columns, with the right types, as the csv-file.
I’m using PostgreSQL 11
Kind regards,
Paul

Re: Two small questions re/ COPY CSV data into table

[Andrew Gierth]

> "Matthias" == Matthias Apitz  writes:

 Matthias> sed 's/|/\v/g' < table-from-Sybase  | sed 's/\\\v/|/g' > 
table-for-copy
 
 >> What on earth is this supposed to achieve?

 Matthias> It first translates any char '|' to vtab and then any '\vtab'
 Matthias> (i.e. a backslash followed by a vtab) back to the char |

 Matthias> The new DELIMITER for PG is then vtab and the | is just a char in the
 Matthias> data (in the example above a pipe of two UNIX cmd).

Yes, but why?

If you use COPY ... WITH DELIMITER '|' (i.e. text mode, not CSV mode)
then the \| is accepted as being a literal | and the unescaped | is
treated as a delimiter. What is the point of the substitutions?

-- 
Andrew (irc:RhodiumToad)

Query very different speeds on seemingly similar data

[Rob Northcott]

I've got a weird problem that I can't work out...
A customer was complaining that their system was running slowly.  They've only 
been using it for a few days, so we tested it on our copy of their data and 
it's running at normal speed.
Uploaded our backup to the live server and it's still quick.
Restored a new backup of their live database on our local test server and it's 
REALLY slow.  So the problem seems to be data-related.
I've run Analyse, Vacuum and Reindex and still no change.
I've stripped the query down to the bare minimum that causes the speed 
difference.
The only difference between the "good" data and the "bad" data involved in this 
query is about 80 extra records in an invoice table (which had 250,000 records 
to start with).

It's behaving (in my opinion) like it does if Analyse isn't run after restoring 
data, or as if the indexes are broken.  Explain Analyse shows it is running the 
query in completely different ways on the two databases.

The query, now I've stripped it down to the offending part, is as follows:
SELECT stk_key,
(SELECT SUM(stdp_quantity) FROM sales_invoicedetails_purchlinks
LEFT JOIN sales_invoicedetails ON std_unique = stdp_std_unique
WHERE stdp_loc_key = '__NBI' AND std_stk_key = stock.stk_key
  ) as level
FROM stock

Table "stock" has about 5000 records, sales_invoicedetails has about 250,000, 
sales_invoicedetails_purchlinks has about 80

The bit that kills it is the "std_stk_key=stock_stk_key" in the sub-query.

On the "good" data it runs in less than 100 milliseconds, on the "bad" data it 
takes ten minutes!

Explain files attached (assuming attachments will get through to the group - 
otherwise what's the best way to post it?)

I'm completely stumped - any suggestions most welcome!

Med vänlig hälsning / Best Regards

Rob Northcott
Software Developer (UK Office, formerly TEAM Systems)

Phone   +44 1752 712052

Compilator AB
Södergatan 22
SE-211 34 Malmö
Sweden
www.compilator.com

[Asset 2@2x]

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY 
MATERIAL AND IS THUS FOR USE ONLY BY THE INTENDED RECIPIENT. IF YOU RECEIVED 
THIS IN ERROR, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL AND ITS 
ATTACHMENTS FROM ALL COMPUTERS.



explain_plan_slow.svg
Description: explain_plan_slow.svg


explain_plan_fast.svg
Description: explain_plan_fast.svg

Re: One way replication in PostgreSQL

[Frank Alberto Rodriguez]

The FDW is a PostgreSQL extension  to connect to other server from
PosgreSQL server inside, with this solution you only need connections
from P to S and no need a third server (external server), just use
triggers to push the INSERT/UPDATE/DELETE information you want to
replicate from P to S through Foreign Data Wrapper.
If you need integrity on the replicated information then I suggest to
use a control table to store the actions to replicate for the case when
it fails you can keep trying til the action succeeds.
Regards 
On Tue, 2019-06-04 at 09:02 +0200, PALAYRET Jacques wrote:
> Hello,
> 
> Thanks a lot for the suggested solutions.
> 
> So, I can use WAL-shipping replication from Primary to the Secundary
> server, but it's only for full replication.
> 
> Let's call " P " the provider/primary/master  and " S " the
> subscriber/secundary/slave one.
> For partial replication (not all the tables), the solutions should
> use a third (intermediate / middle) server which could have both ways
> flow with the server P but only one way flow towards the server S.
> For example, a logical replication (pglogical or logical Postgresql
> replication) between server P and the middle server and then a WAL-
> shipping replication between middle server and server S.
> Is that right ?
> 
> About the " FDW " solution in " an external server " (a middle one),
> is it possible to integrate the FDW in the P server to avoid the "
> external server " ?
> 
> => What about the trigger-based replication systems like Slony or
> Londiste ; is it really necessary to have a connection or flow from
> the server S towards the server P ?
> 
> Regards
> De: "PALAYRET Jacques" 
> À: pgsql-general@lists.postgresql.org
> Envoyé: Lundi 3 Juin 2019 18:00:51
> Objet: One way replication in PostgreSQL
> 
> Hello,
> 
> If, for security reasons, I can't create a connection or a flow from
> subscriber/secundary/slave towards provider/primary/master, witch
> replication systems can I use ?
> 
> If possible, I would prefer partial replication (only some tables) to
> full base replication (all instances).
> 
> Do trigger-based replication systems (like Slony or Londiste or
> others) need a connection or flow from subscriber to the provider ?
> 
> Thanks in advance
> - Météo-France -
> PALAYRET JACQUES
> DCSC/MBD 
> jacques.palay...@meteo.fr 
> Fixe : +33 561078319

Re: Two small questions re/ COPY CSV data into table

[Matthias Apitz]

El día Tuesday, June 04, 2019 a las 04:41:47PM +0100, Andrew Gierth escribió:

>  Matthias> I do load this now with COPY in mode TEXT and modify the data
>  Matthias> before with:
> 
>  Matthias>  sed 's/|/\v/g' < table-from-Sybase  | sed 's/\\\v/|/g' > 
> table-for-copy
> 
> What on earth is this supposed to achieve?

It first translates any char '|' to vtab and then any '\vtab' (i.e. a
backslash followed by a vtab) back to the char |

The new DELIMITER for PG is then vtab and the | is just a char in the
data (in the example above a pipe of two UNIX cmd). Do you do UNIX?

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: Long running query - connection and keepalives enabled but query not canceled

[Laurenz Albe]

Tomasz Ostrowski wrote:
> I have database users which have to run some ad-hoc queries on the 
> database. These queries sometimes are pretty long running, especially 
> when a user messes up a join condition for two 1Grows tables and 
> therefore tries to count the number of quarks in the universe or 
> something like this.
> 
> After a query fails to return result for some time the user sometimes 
> just closes his laptop lid, dropping his VPN connection and goes home to 
> watch some cats on the Internet.
> 
> The TCP keepalives functionality on the database server's operating 
> system would figure out that the connection is not working anymore and 
> close it.
> 
> You'd assume that the database would get the info and cancel the query. 
> Except - it does not.
> 
> The database would get an error on the connection socket only after it 
> tries to read or write to it next time. But it does not try to do this - 
> it's busy counting those quarks until statement_timeout is reached.

By default "tcp_keepalives_idle" is quite large: 2 hours.

Are you sure that the queries keep running for longer than that?

Try to experiment with lower settings.  It will cause marginally more
network traffic, but dead connections will be detected more quickly.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Long running query - connection and keepalives enabled but query not canceled

[Tomasz Ostrowski]

Hi.

I have database users which have to run some ad-hoc queries on the 
database. These queries sometimes are pretty long running, especially 
when a user messes up a join condition for two 1Grows tables and 
therefore tries to count the number of quarks in the universe or 
something like this.


After a query fails to return result for some time the user sometimes 
just closes his laptop lid, dropping his VPN connection and goes home to 
watch some cats on the Internet.


The TCP keepalives functionality on the database server's operating 
system would figure out that the connection is not working anymore and 
close it.


You'd assume that the database would get the info and cancel the query. 
Except - it does not.


The database would get an error on the connection socket only after it 
tries to read or write to it next time. But it does not try to do this - 
it's busy counting those quarks until statement_timeout is reached.


But default statement_timeout has to be quite large - the databases are 
large (up to several TB) and are sometimes exported with pg_dump, 
restored etc. - 24 hours is maybe a reasonable setting.


Is there a way to configure the database somehow, so it would figure out 
that the connection is closed, faster than statement_timeout?


I'd think maybe a periodic signal to the connection process asking for 
validating its connections somehow if it's running a query longer than 
tcp_keepalives_idle+tcp_keepalives_count*tcp_keepalives_interval?


I think it would be possible to work-around with some external process 
checking /proc/[PID]/net/tcp[6] for closed connections and 
pg_cancel_backend() them. But it's managed PostgreSQL (AWS RDS), so it's 
not allowed.


Is there any possibility to have something like this added to 
PostgreSQL? I believe it's not just us having this problem.


--
Tomasz "Tometzky" Ostrowski

Re: Two small questions re/ COPY CSV data into table

[Andrew Gierth]

> "Matthias" == Matthias Apitz  writes:

 >> That sounds like the file is not actually a CSV - why do you think
 >> it is?

 Matthias> Well, it's not strictly CSV,

In other words it's not a CSV at all.

 Matthias> I do load this now with COPY in mode TEXT and modify the data
 Matthias> before with:

 Matthias>  sed 's/|/\v/g' < table-from-Sybase  | sed 's/\\\v/|/g' > 
table-for-copy

What on earth is this supposed to achieve?

-- 
Andrew (irc:RhodiumToad)

Re: Two small questions re/ COPY CSV data into table

[Matthias Apitz]

El día Tuesday, June 04, 2019 a las 11:32:45AM +0100, Andrew Gierth escribió:

>  Matthias> 2. The CSV export is done by some Sybase tool which escapes
>  Matthias> the delimiter as '\|', i.e. putting a backslash before the
>  Matthias> delimiter. I found no way that COPY understands this
>  Matthias> excaping. Any ideas?
> 
> That sounds like the file is not actually a CSV - why do you think it
> is?
> 
> ...

Well, it's not strictly CSV, but it is what the Sybase tool produces. The
delimiter is set to '|' and a data example line looks like:

0|1| cat $1 \| lpr -Pprinter |3|4

I do load this now with COPY in mode TEXT and modify the data before
with:

 sed 's/|/\v/g' < table-from-Sybase  | sed 's/\\\v/|/g' > table-for-copy

Works fine.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: Questions about btree_gin vs btree_gist for low cardinality columns

[Jeremy Finzel]

On Sun, Jun 2, 2019 at 6:07 PM Tom Lane  wrote:

> Anyway, the larger point here is that right now btree_gin is just a quick
> hack, and it seems like it might be worth putting some more effort into
> it, because the addition of duplicate-compression changes the calculus
> for whether it's useful.
>

Thank you to all for the thoughtful and thorough replies!

To clarify, I am definitely more interested in "low cardinality" in the
sense NOT of a boolean or very few values, but rather enough values to
where:

   1. It's selective enough that having *some kind of index* actually will
   significantly speed up queries vs. a sequential scan
   2. There are too many values to use partial indexes easily without it
   becoming a kind of maintenance nightmare

In our environment, we happen to have this kind of situation a lot.  For
example, account codes, or other foreign key ids to lookup tables that
categorize data in some way that is frequently queried, exist on tables
with upwards of 100 million rows.  Typically it may be something like 50 to
500 unique values.

Historically, we have just had a bunch of regular btree indexes on these,
and there are quite a lot of them indeed.

Here is my specific example:

   - Table has 127 million rows, including a toast field.  The table is
   270GB
   - The filter is on a field with only 16 unique values.
   - The actual filter condition is filtering a join to 4 of the 16 unique
   values

As I said, performance was nearly identical for btree vs. gin.  But I was
much happier with the memory usage of GIN given its tiny size:

   - Memory for join using btree: Buffers: shared hit=12 read=328991
   - Memory for join using gin: Buffers: shared hit=12 read=13961

The btree index here is 3.8GB, whereas the gin index is only 200MB.  But I
have not tested how either index handles bloat.

Morris, I think the reason your gin index was clearly slower was precisely
because your example uses an index-only scan - which gin does not support.

Thanks,
Jeremy

Re: Research on ?? operators

[Steve Atkins]

> On Jun 4, 2019, at 1:35 PM, Michael Lewis  wrote:
> 
> ":foo" named placeholders
> 
> If I may, is this supported natively in Postgres prepared statements?

It's not. The only type we support are numbered $1 type placeholders.

> Can I see an example? I do not care much for the numbers of positional 
> placeholders and would love to use names instead if possible.

It'd be nice. They're supported via rewriting at the driver level in some 
drivers, and I've written shims to convert them in an app a few times and it 
makes for much more readable - and bug-resistant - code.

Supporting it at the protocol level would be very nice.

Cheers,
  Steve

Re: Research on ?? operators

[Matteo Beccati]

Hi Michael,

On 04/06/2019 14:35, Michael Lewis wrote:
> /":foo" named placeholders/
> 
> If I may, is this supported natively in Postgres prepared statements?
> Can I see an example? I do not care much for the numbers of positional
> placeholders and would love to use names instead if possible.

Not natively. In this case it's the PHP PDO pgsql driver that performs
the translation.

I suppose many other DB libraries do that in any programming language.


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Re: Research on ?? operators

[Michael Lewis]

*":foo" named placeholders*

If I may, is this supported natively in Postgres prepared statements? Can I
see an example? I do not care much for the numbers of positional
placeholders and would love to use names instead if possible.

Re: Research on ?? operators

[Matteo Beccati]

Hi Steve,

On 04/06/2019 12:49, Steve Atkins wrote:
> Doubling "?" to "??" isn't an obvious, intuitive way to do that, at least
> to me. Maybe it would be to people coming from Java.

Funny enough, when I proposed \? on the pg mailing lists a few years ago
I was suggested ?? to match SQL escaping and Hibernate (going from memory).

> Perl's DBD::Pg deals with it in two ways. One is to allow escaping
> the ? with a backslash - so "?" is a placeholder, while "\?" is passed as
> "?" to the database. That's more consistent with other languages, and
> I think you're far less likely to ever see a backslash in a custom operator
> than "??".

That is true as well, but '\' being also the escape character for
strings would also lead to some WTF moments if one tried to escape the
backslash itself, e.g. '\\? === '\?'.

> The other is that it supports the postgresql standard placeholders - $1,
> $2 etc. - which avoid the issue and are more flexible. It also has a 
> configuration
> option to completely ignore "?" in queries, so "$1" is a placeholder and "?"
> is passed through to the database.
> 
> (Though I like the ":foo" or "@foo" style named placeholders best)

PDO does support both positional "?" and ":foo" named placeholders.
Adding an option to disable one would probably break much more code and
libraries than just a simple escape character.


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Re: Research on ?? operators

[Matteo Beccati]

Hi Andrew,

On 04/06/2019 12:38, Andrew Gierth wrote:
>> "Matteo" == Matteo Beccati  writes:
> 
>  Matteo> https://wiki.php.net/rfc/pdo_escape_placeholders
> 
>  Matteo> One question that I'm supposed to answer now is: is there any
>  Matteo> known usage in the wild of some custom "??" operator that would
>  Matteo> require funny escaping like ""?
> 
> I don't recall seeing a ?? operator in the wild, but it is a perfectly
> legal operator name and you should assume that it exists somewhere.

Absolutely yes, that's why it is mentioned in my RFC and will be
eventually mentioned in the UPGRADE documentation.

But perhaps we can also safely assume that 99.9% of the users won't have
any operator containing "??", so it can be considered a minor BC-break.


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Re: Research on ?? operators

[Steve Atkins]

> On Jun 4, 2019, at 10:00 AM, Matteo Beccati  wrote:
> 
> Hello generals,
> 
> I'm trying to resurrect a change to the PHP PDO driver to allow using
> the "?" json operator (and other operators containing a question mark),
> without it being interpreted as a placeholder for a query argument. In
> order to do so, like Hibernate, I went for the double "??" escaping:
> 
> https://wiki.php.net/rfc/pdo_escape_placeholders
> 
> One question that I'm supposed to answer now is: is there any known
> usage in the wild of some custom "??" operator that would require funny
> escaping like ""?

I don't know of one today, but that doesn't mean there isn't or won't
be tomorrow.

> I've tried to search pgxn but couldn't find any match, so I thought it
> was worth to try and ask here.

Doubling "?" to "??" isn't an obvious, intuitive way to do that, at least
to me. Maybe it would be to people coming from Java.

Perl's DBD::Pg deals with it in two ways. One is to allow escaping
the ? with a backslash - so "?" is a placeholder, while "\?" is passed as
"?" to the database. That's more consistent with other languages, and
I think you're far less likely to ever see a backslash in a custom operator
than "??".

The other is that it supports the postgresql standard placeholders - $1,
$2 etc. - which avoid the issue and are more flexible. It also has a 
configuration
option to completely ignore "?" in queries, so "$1" is a placeholder and "?"
is passed through to the database.

(Though I like the ":foo" or "@foo" style named placeholders best)

Cheers,
  Steve

Re: Research on ?? operators

[Andrew Gierth]

> "Matteo" == Matteo Beccati  writes:

 Matteo> https://wiki.php.net/rfc/pdo_escape_placeholders

 Matteo> One question that I'm supposed to answer now is: is there any
 Matteo> known usage in the wild of some custom "??" operator that would
 Matteo> require funny escaping like ""?

I don't recall seeing a ?? operator in the wild, but it is a perfectly
legal operator name and you should assume that it exists somewhere.

-- 
Andrew (irc:RhodiumToad)

Re: Two small questions re/ COPY CSV data into table

[Andrew Gierth]

> "Matthias" == Matthias Apitz  writes:

 Matthias>... WITH DELIMITER '|', FORMAT CSV ;

I think you misread the docs; the new-style syntax would be

COPY ... WITH (DELIMITER '|', FORMAT CSV);

where the parens are not optional. The old-style syntax with no parens
after WITH is what you were using before.

 Matthias> 2. The CSV export is done by some Sybase tool which escapes
 Matthias> the delimiter as '\|', i.e. putting a backslash before the
 Matthias> delimiter. I found no way that COPY understands this
 Matthias> excaping. Any ideas?

That sounds like the file is not actually a CSV - why do you think it
is?

PG accepts two formats (actually 3 if you count binary format which is
rarely used):

TEXT: records are delimited by newlines, columns are delimited by a
delimiter character (default tab), there are no quotation marks, any
newline, backslash, or delimiter in the data must be escaped as a
backslash-sequence (e.g. \n or \012)

CSV: columns may be quoted (in which case delimiters and newlines inside
them are ignored), records are delimited by newlines _outside_ quoted
fields, there are no backslash-sequences or escapes outside of quoted
fields, quote characters inside quoted fields are doubled (though
there's an option to change this).

PG follows the CSV spec at https://www.ietf.org/rfc/rfc4180.txt fairly
closely.

-- 
Andrew (irc:RhodiumToad)

Two small questions re/ COPY CSV data into table

[Matthias Apitz]

Hello,

I'm using COPY to load CSV data into the table in PG 10.x and have to simple
questions:

1. I do use as command:

   COPY adm_cat FROM '/home/sisis/PostgreSQL/PDcsv/adm_cat.load' WITH DELIMITER 
'|' CSV ;

   which works fine. When I read the PG docs the syntax should be

   ... WITH DELIMITER '|', FORMAT CSV ;

   But this gives an syntax error as:

   LINE 1: .../PostgreSQL/PDcsv/adm_cat.load' WITH DELIMITER '|', FORMAT C...

   and the ',' is marked as its place. Why?
  
2. The CSV export is done by some Sybase tool which escapes the
   delimiter as '\|', i.e. putting a backslash before the delimiter. I
   found no way that COPY understands this excaping. Any ideas?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: psql \copy

[Pavel Stehule]

Hi

út 4. 6. 2019 v 10:50 odesílatel  napsal:

> Hi. I’m trying to execute this command:
>
> C:\P_bin\psql -h 10.233.37.24 -p 5432 -U tester -d test -w  -c "\copy
> public.BadenWuerttemberg_20181011 FROM C:\data\Baden-Wuerttemberg.csv' WITH
> DELIMITER ';' CSV"
>
> Error:  relation "public.badenwuerttemberg_20181011" does not exist.
>
> What am I Doing wrong?
>

looks like case sensitive identifier is used. try

"\copy public.""BadenWuerttemberg_20181011"" ...

Regards

Pavel Stehule



>
> There is a table (badenwuerttemberg_20181011) in database test under the
> public schema. The table has all the columns, with the right types, as the
> csv-file.
>
> I’m using PostgreSQL 11
>
> Kind regards,
>
> Paul
>

Research on ?? operators

[Matteo Beccati]

Hello generals,

I'm trying to resurrect a change to the PHP PDO driver to allow using
the "?" json operator (and other operators containing a question mark),
without it being interpreted as a placeholder for a query argument. In
order to do so, like Hibernate, I went for the double "??" escaping:

https://wiki.php.net/rfc/pdo_escape_placeholders

One question that I'm supposed to answer now is: is there any known
usage in the wild of some custom "??" operator that would require funny
escaping like ""?

I've tried to search pgxn but couldn't find any match, so I thought it
was worth to try and ask here.


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/

psql \copy

[paul.malm]

Hi. I'm trying to execute this command:
C:\P_bin\psql -h 10.233.37.24 -p 5432 -U tester -d test -w  -c "\copy 
public.BadenWuerttemberg_20181011 FROM C:\data\Baden-Wuerttemberg.csv' WITH 
DELIMITER ';' CSV"
Error:  relation "public.badenwuerttemberg_20181011" does not exist.
What am I Doing wrong?

There is a table (badenwuerttemberg_20181011) in database test under the public 
schema. The table has all the columns, with the right types, as the csv-file.
I'm using PostgreSQL 11
Kind regards,
Paul

Re: One way replication in PostgreSQL

[Achilleas Mantzios]

On 4/6/19 10:02 π.μ., PALAYRET Jacques wrote:

Hello,

Thanks a lot for the suggested solutions.

So, I can use WAL-shipping replication from Primary to the Secundary server, 
but it's only for full replication.

Let's call " P " the provider/primary/master  and " S " the 
subscriber/secundary/slave one.
For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the 
server S.

For example, a logical replication (pglogical or logical Postgresql 
replication) between server P and the middle server and then a WAL-shipping 
replication between middle server and server S.
Is that right ?

About the " FDW " solution in " an external server " (a middle one), is it possible to 
integrate the FDW in the P server to avoid the " external server " ?

=> What about the trigger-based replication systems like Slony or Londiste ; is 
it really necessary to have a connection or flow from the server S towards the 
server P ?


I'll describe our solution here, but its a far fetched approach, a diving into 
uncharted waters situation, but for us it was the only solution back in early 
2000.
In one word : The long forgotten DBMirror (Mentioned during "Postgresql 11 : what's old" comments that followed the presentation in Lisbon pgconf2018.eu) . You can setup your triggers, produce your 
SQL files, sent them to S whatever method you wish and run them without any connectivity from S to P.

Setting up is very easy. However you must be prepared to carry this as "technical 
debt" in future upgrades.
No Slony expert here, but in https://severalnines.com/blog/experts-guide-slony-replication-postgresql it is mentioned that you can ship in the form of SQL files as well. So you setup a slony slave 
inside your P system and produce SQL files which you replay to your remote S, with no connectivity to P.




Regards

*De: *"PALAYRET Jacques" 
*À: *pgsql-general@lists.postgresql.org
*Envoyé: *Lundi 3 Juin 2019 18:00:51
*Objet: *One way replication in PostgreSQL

Hello,

If, for security reasons, I can't create a connection or a flow from 
subscriber/secundary/slave towards provider/primary/master, witch replication 
systems can I use ?

If possible, I would prefer partial replication (only some tables) to full base 
replication (all instances).

Do trigger-based replication systems (like Slony or Londiste or others) need a 
connection or flow from subscriber to the provider ?

Thanks in advance
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: One way replication in PostgreSQL

[Guillaume Lelarge]

Le mar. 4 juin 2019 à 09:03, PALAYRET Jacques  a
écrit :

> Hello,
>
> Thanks a lot for the suggested solutions.
>
> So, I can use WAL-shipping replication from Primary to the Secundary
> server, but it's only for full replication.
>
>
Yes.

Let's call " P " the provider/primary/master  and " S " the
> subscriber/secundary/slave one.
> For partial replication (not all the tables), the solutions should use a
> third (intermediate / middle) server which could have both ways flow with
> the server P but only one way flow towards the server S.
> For example, a logical replication (pglogical or logical Postgresql
> replication) between server P and the middle server and then a WAL-shipping
> replication between middle server and server S.
> Is that right ?
>
>
That could work.

About the " FDW " solution in " an external server " (a middle one), is it
> possible to integrate the FDW in the P server to avoid the " external
> server " ?
>
>
Not sure I understand where you're going here.

=> What about the trigger-based replication systems like Slony or Londiste
> ; is it really necessary to have a connection or flow from the server S
> towards the server P ?
>
>
As far as I remember, with Slony, P must be able to connect to S, and S
must be able to connect to P.


-- 
Guillaume.
Dalibo.

Re: One way replication in PostgreSQL

[PALAYRET Jacques]

Hello, 

Thanks a lot for the suggested solutions. 

So, I can use WAL-shipping replication from Primary to the Secundary server, 
but it's only for full replication. 

Let's call " P " the provider/primary/master and " S " the 
subscriber/secundary/slave one. 
For partial replication (not all the tables), the solutions should use a third 
(intermediate / middle) server which could have both ways flow with the server 
P but only one way flow towards the server S. 
For example, a logical replication (pglogical or logical Postgresql 
replication) between server P and the middle server and then a WAL-shipping 
replication between middle server and server S. 
Is that right ? 

About the " FDW " solution in " an external server " (a middle one), is it 
possible to integrate the FDW in the P server to avoid the " external server " 
? 

=> What about the trigger-based replication systems like Slony or Londiste ; is 
it really necessary to have a connection or flow from the server S towards the 
server P ? 

Regards 

De: "PALAYRET Jacques"  
À: pgsql-general@lists.postgresql.org 
Envoyé: Lundi 3 Juin 2019 18:00:51 
Objet: One way replication in PostgreSQL 

Hello, 

If, for security reasons, I can't create a connection or a flow from 
subscriber/secundary/slave towards provider/primary/master, witch replication 
systems can I use ? 

If possible, I would prefer partial replication (only some tables) to full base 
replication (all instances). 

Do trigger-based replication systems (like Slony or Londiste or others) need a 
connection or flow from subscriber to the provider ? 

Thanks in advance 
- Météo-France - 
PALAYRET JACQUES 
DCSC/MBD 
jacques.palay...@meteo.fr 
Fixe : +33 561078319 

Re: csvlog Behavior when log file missing

[Laurenz Albe]

Lesley Kimmel wrote:
> I have a system where postgres seems to be randomly dying. When researching 
> the issue
> I, of course, began looking at logs.
> 
> I found that much of the logs were missing due to logrotate configuration on 
> the server.
> Logs were being rotated by size and then compressed. Because of this, the 
> first rotation
> would take the log file out from underneath the database and it is not 
> recreated.
> 
> My suspicion is that the database eventually crashes due to inability to log.
> The documentation says the logging_collector will halt other processes if it 
> is
> unable to log. However, it seems that the system continues to run fine for 
> several
> days before crashing and applications using the database continue to operate.
> 
> Does the logrotate configuration possibly explain the behavior? I can add the
> 'copytruncate' option to the logrotate configuration and see if the issue 
> resolves,
> but I'd like to also understand the issue. Also, if I add the 'stderr' 
> destination
> and continue to rotate the csv log file out, might I catch errors related to 
> the
> missing csv log or would the logging_collector simply  continue operating 
> since it
> still has access to the stderr log?

To diagnose the problem, why don't you disable logrotate for a while?

Then you can see if it is part of the problem, but above all nothing will
mess with your log files and you should be able to find out more about the 
problem.

I never use logrotate with PostgreSQL, but set "log_filename" to 
"postgresql-%a.log"
or "postgresql-%d.log", "log_truncate_on_rotation" to "on" and 
"log_rotation_size" to
"0".  Then PostgreSQL rotates the log by itself.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com