Re: [GENERAL] keeping WAL after dropping replication slots

[Chris Mair]

Postgres version?

9.6.1


Have you considered upgrading to 9.6.2?
There were some fixes, including WAL related:

https://www.postgresql.org/docs/9.6/static/release-9-6-2.html

Not exactly regarding what you see, though...

Bye,
Chris.





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

Re: [GENERAL] keeping WAL after dropping replication slots

[Tom DalPozzo]

Hi,
2017-04-06 21:51 GMT+02:00 Adrian Klaver :

> On 04/04/2017 11:52 PM, Tom DalPozzo wrote:
>
>> Hi,
>>
>> 2017-04-05 1:55 GMT+02:00 Adrian Klaver > >:
>>
>> On 04/04/2017 07:45 AM, Tom DalPozzo wrote:
>>
>> Postgres version?
>>
>> 9.6.1
>>
>>
>> Hi,
>> I had two replication slots on my primary. Slaves off and
>> (around 800)
>> WALs kept as expected.
>>
>>
>> Slaves off means?:
>>
>>
>> You replication set up from the master to the slaves(how many?).
>> Then you disconnected the slaves how?
>>
>> I have 2 slaves configured with async replication but they were down
>>  when I dropped the slots.
>>
>> So the 800 WALs number mean you have wal_keep_segments set to 800?
>>
>> No,  wal_keep_segments is commented.
>> 800 is the rough number of files I saw in xlog dir before dropping the
>> slots.
>>
>
> What are your settings for?:
>
> archive_mode
>
archive_mode is off


> archive_command
>
it's set as I tested it some months ago but now archive_mode is off


> Do you see anything in the Postgres log that might apply?
>
No, nothing


>
>>
>>
>>
>> I dropped those slots but over time, the system kept on adding
>> new WALs
>> without reusing them or deleting them.
>> Only after shutdown and restart the system deleted those WAL
>> files.
>> Is that ok?
>> regards
>> Pupillo
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>> Regards
>> Pupillo
>>
>> Thanks
Pupillo

Re: [GENERAL] A change in the Debian install

[Adrian Klaver]

On 04/06/2017 08:01 PM, rob stone wrote:



On Thu, 2017-04-06 at 15:58 -0700, Adrian Klaver wrote:

On 04/06/2017 03:16 PM, rob stone wrote:





Which is what has me confused. If you are using the postgresql-
common
system then the *.conf files should be in
/etc/postgresql/version/cluster_name/.

Where exactly is PGDATA and why is the *.conf file there?



It's always been in the PGDATA path and it has always worked.
The doco implies that PGDATA is the place for the *.conf files unless
overridden by the config_file parameter.


That is the default location and is generally the case in a source 
install. Package maintainers can and do often put them elsewhere. AFAIK 
the Debian/Ubuntu way using postgresql-common is to put them in 
/etc/postgresql/version/cluster_name/. So on one of my Ubuntu 16.04 
installs with Postgres 9.6.2:


aklaver@arkansas:~$ l /etc/postgresql/9.6/main/
total 56
drwxr-xr-x 2 postgres postgres  4096 Feb 11 16:23 ./
drwxr-xr-x 3 postgres postgres  4096 Feb 11 07:15 ../
-rw-r--r-- 1 postgres postgres   315 Feb 11 07:15 environment
-rw-r--r-- 1 postgres postgres   143 Feb 11 07:15 pg_ctl.conf
-rw-r- 1 postgres postgres  4642 Feb 11 16:23 pg_hba.conf
-rw-r- 1 postgres postgres  1636 Feb 11 07:15 pg_ident.conf
-rw-r--r-- 1 postgres postgres 22438 Feb 11 16:11 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Feb 11 07:15 start.conf


and PGDATA:

aklaver@arkansas:~$ sudo ls -al /var/lib/postgresql/9.6/main/
total 92
drwx-- 19 postgres postgres 4096 Apr  6 15:54 .
drwxr-xr-x  3 postgres postgres 4096 Feb 11 07:15 ..
drwx--  6 postgres postgres 4096 Feb 11 16:06 base
drwx--  2 postgres postgres 4096 Mar 22 12:22 global
drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_clog
drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_commit_ts
drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_dynshmem
drwx--  4 postgres postgres 4096 Feb 11 07:15 pg_logical
drwx--  4 postgres postgres 4096 Feb 11 07:15 pg_multixact 



drwx--  2 postgres postgres 4096 Mar 22 12:21 pg_notify 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_replslot 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_serial 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_snapshots 



drwx--  2 postgres postgres 4096 Mar 22 12:21 pg_stat 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_stat_tmp 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_subtrans 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_tblspc 



drwx--  2 postgres postgres 4096 Feb 11 07:15 pg_twophase 



-rw---  1 postgres postgres4 Feb 11 07:15 PG_VERSION 



drwx--  3 postgres postgres 4096 Feb 11 07:15 pg_xlog 



-rw---  1 postgres postgres   88 Feb 11 07:15 postgresql.auto.conf 



-rw---  1 postgres postgres  133 Mar 22 12:21 postmaster.opts 



-rw---  1 postgres postgres  100 Mar 22 12:21 postmaster.pid


This is why I am trying to figure out if you are using the Debian packaging:

A) What is your PGDATA?

B) How postgresql.conf got there?

The reason it is important is that the next update will probably land 
you back in the same situation that started this thread, unless we 
figure out what is going on.


Another question:

Have you ever installed Postgres on this machine using something other 
then the Debian packages?




Cheers,
Rob




--
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] A change in the Debian install

[rob stone]

On Thu, 2017-04-06 at 15:58 -0700, Adrian Klaver wrote:
> On 04/06/2017 03:16 PM, rob stone wrote:
> > 
> > 
> 
> Which is what has me confused. If you are using the postgresql-
> common 
> system then the *.conf files should be in 
> /etc/postgresql/version/cluster_name/.
> 
> Where exactly is PGDATA and why is the *.conf file there?
> 

It's always been in the PGDATA path and it has always worked.
The doco implies that PGDATA is the place for the *.conf files unless
overridden by the config_file parameter.

Cheers,
Rob


-- 
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] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-07 14:19 GMT+12:00 David G. Johnston :

> On Thu, Apr 6, 2017 at 7:15 PM, Patrick B 
> wrote:
>
>>
>> David,
>> That won't work.
>>
>
> ​Actually, it works fine, you just keep moving the under-specified problem
> space.
> ​
> I'd suggest creating a self-contained running example that gets you close
> and show what the final output should be.
>
> David J.
>
>
http://sqlfiddle.com/#!15/6d65d

There is an example. Please note the `col2` returns not only 'main'. I need
it to return only main and I need to put it in a where clause. I can't use
~ operator because I will need to do something like:

FROM test1 t1
> JOIN another_view AS s
> WHERE s.full_path = substr(t1.full_path, char_pos '/file..' to get
> /filesuser/client/.../attachment/.../) || 'main'


Thanks
Patrick.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[David G. Johnston]

On Thu, Apr 6, 2017 at 7:15 PM, Patrick B  wrote:

>
> David,
> That won't work.
>

​Actually, it works fine, you just keep moving the under-specified problem
space.
​
I'd suggest creating a self-contained running example that gets you close
and show what the final output should be.

David J.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Arjen Nienhuis]

On Apr 7, 2017 4:16 AM, "Patrick B"  wrote:



2017-04-07 14:08 GMT+12:00 David G. Johnston :

> On Thu, Apr 6, 2017 at 6:33 PM, Patrick B 
> wrote:
>
>> When actually I just want the 'main''
>>
>
> ​SELECT * FROM tbl WHERE path_name ~ '/main$' ?
>
> David J.
> ​
>


David,
That won't work.

When performing the select, I got:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/main

when actually i want:

/{s3bucket}/filesuser/client/27801123/attachment/4510/main


Patrick.


WHERE (path_name)::text ~ '^/filesuser/client/\d+/attachment/\d+/main$'

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Arjen Nienhuis]

On Apr 7, 2017 4:08 AM, "David G. Johnston" 
wrote:

On Thu, Apr 6, 2017 at 6:33 PM, Patrick B  wrote:

> When actually I just want the 'main''
>

​SELECT * FROM tbl WHERE path_name ~ '/main$' ?

David J.
​


Or just:

SELECT 'main';

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-07 14:08 GMT+12:00 David G. Johnston :

> On Thu, Apr 6, 2017 at 6:33 PM, Patrick B 
> wrote:
>
>> When actually I just want the 'main''
>>
>
> ​SELECT * FROM tbl WHERE path_name ~ '/main$' ?
>
> David J.
> ​
>


David,
That won't work.

When performing the select, I got:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/main

when actually i want:

/{s3bucket}/filesuser/client/27801123/attachment/4510/main


Patrick.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[David G. Johnston]

On Thu, Apr 6, 2017 at 6:33 PM, Patrick B  wrote:

> When actually I just want the 'main''
>

​SELECT * FROM tbl WHERE path_name ~ '/main$' ?

David J.
​

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-06 18:10 GMT+12:00 Patrick B :

>
> 2017-04-06 17:35 GMT+12:00 Arjen Nienhuis :
>
>>
>>
>> On Apr 6, 2017 05:57, "Patrick B"  wrote:
>>
>> Hi guys,
>>
>> i've got this column:
>>
>> path_name character varying(255)
>>>
>>
>> I store full S3 bucket path for the attachments of my application on it;
>> example:
>>
>> /{s3bucket}/filesuser/client/27801123/attachment/4510/main
>>>
>> /{s3bucket}/filesuser/client/27801123/attachment/4510/file
>>>
>>
>>
>> I wanna do a select, where path_name has only 'main' and not anything
>> else.
>>
>>
>> WHERE path_nane LIKE '%/main'
>>
>>
>>
>
> I was able to do it like this:
>
>
> WHERE (path_name)::text ~ '^\/filesuser\/client/\d+/(
>> attachment)/\d+/(main)+'
>
>
>
> Thanks
> Patrick.
>



 Guys.. sorry but actually it's not exactly what I'm looking for:

The paths */{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file* are the root
path for:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/
1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801123/attachment/4510/main/
111/small/photo.jpg


So for that single file (photo.jpg) i can have:

> /{s3bucket}/filesuser/client/27801123/attachment/4510/file/
> 1113/small/photo.jpg
> /{s3bucket}/filesuser/client/27801124/attachment/4511/main/
> 111/small/photo.jpg
> /{s3bucket}/filesuser/client/27801125/attachment/4512/file
> /{s3bucket}/filesuser/client/27801126/attachment/4513/main



select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;

It works, but I get :

photo.jpg
> main
> file


When actually I just want the 'main''

How can I include this regexp in a where clause? Something like:

>
> WHERE REGEXP_REPLACE(path_name, '.*/', '')::text = 'main'


Thanks!
Patrick.

Re: [GENERAL] A change in the Debian install

[Adrian Klaver]

On 04/06/2017 03:16 PM, rob stone wrote:



On Wed, 2017-04-05 at 21:14 -0700, Adrian Klaver wrote:


What repos are you using, the Debian or the PGDG one?

I guess the question I should really ask is, are you using a repo or
some other method to upgrade?




Upgraded from standard Debian repos.

Nothing else was changed other than the binaries.
It appears that the prior version obtained postgresql.conf from the
PGDATA path whilst the 9.6.2-2 looked in the Postgres /etc paths where
it tried to use the conf file, and as a consequence, the autovacuum
daemon was not started.
I removed the Postgres /etc conf file and as it didn't find one there,
fell back to the PGDATA path and all is well.



That is packaging dependent. When using the Debian/Ubuntu
postgresql-common system the postgresql.conf will be in
/etc/postgresql/version/cluster_name/



I'm quite happy with the standard /etc/postgresql/major_version/main
method of installation. It means you can run different major versions
on the same box.


Which is what has me confused. If you are using the postgresql-common 
system then the *.conf files should be in 
/etc/postgresql/version/cluster_name/.


Where exactly is PGDATA and why is the *.conf file there?




Cheers,
Rob




--
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] A change in the Debian install

[rob stone]

On Wed, 2017-04-05 at 21:14 -0700, Adrian Klaver wrote:
> 
> What repos are you using, the Debian or the PGDG one?
> 
> I guess the question I should really ask is, are you using a repo or 
> some other method to upgrade?
> 
> 

Upgraded from standard Debian repos.

Nothing else was changed other than the binaries.
It appears that the prior version obtained postgresql.conf from the
PGDATA path whilst the 9.6.2-2 looked in the Postgres /etc paths where
it tried to use the conf file, and as a consequence, the autovacuum
daemon was not started.
I removed the Postgres /etc conf file and as it didn't find one there,
fell back to the PGDATA path and all is well.


> That is packaging dependent. When using the Debian/Ubuntu 
> postgresql-common system the postgresql.conf will be in 
> /etc/postgresql/version/cluster_name/
> 

I'm quite happy with the standard /etc/postgresql/major_version/main
method of installation. It means you can run different major versions
on the same box.


Cheers,
Rob


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

Re: [GENERAL] keeping WAL after dropping replication slots

[Adrian Klaver]

On 04/04/2017 11:52 PM, Tom DalPozzo wrote:

Hi,

2017-04-05 1:55 GMT+02:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

On 04/04/2017 07:45 AM, Tom DalPozzo wrote:

Postgres version?

9.6.1


Hi,
I had two replication slots on my primary. Slaves off and
(around 800)
WALs kept as expected.


Slaves off means?:


You replication set up from the master to the slaves(how many?).
Then you disconnected the slaves how?

I have 2 slaves configured with async replication but they were down
 when I dropped the slots.

So the 800 WALs number mean you have wal_keep_segments set to 800?

No,  wal_keep_segments is commented.
800 is the rough number of files I saw in xlog dir before dropping the
slots.


What are your settings for?:

archive_mode

archive_command

Do you see anything in the Postgres log that might apply?






I dropped those slots but over time, the system kept on adding
new WALs
without reusing them or deleting them.
Only after shutdown and restart the system deleted those WAL files.
Is that ok?
regards
Pupillo




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


Regards
Pupillo




--
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] WAL being written during SELECT * query

[David G. Johnston]

On Thu, Apr 6, 2017 at 8:51 AM, Tom DalPozzo  wrote:

>
> What is the meaning of FPI_FOR_HINT?
> ​
>

​Full Page Image for Hint [Bits]

Its noted as being dependent upon checksums being enabled.

I have a feel for the interactions involved here but not enough to explain
them in detail.

David J.

Re: [GENERAL] WAL being written during SELECT * query

[Tom DalPozzo]

2017-04-04 19:18 GMT+02:00 Scott Marlowe :

> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo  wrote:
> > Hi,
> > I have a very big table (10GB).
> > I noticed that many WAL segments are being written when elaborating read
> > only transactions like this:
> > select * from dati256 where id >4300 limit 100;
> > I don't understand why are there WAL writings during read only
> transactions.
> > Regards
> > Pupillo
>
>
>
> I think this is the db setting hint bits, but I'm no expert in that area.
>

Hi,
I'm not able to reproduce the problem in a deterministic way. Sometimes it
does sometimes not.
Anyway, examining with pg_xlogdum those WAL, I get many many records like
these:

rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
C/E502AF28, prev C/E5028F20, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/18846 blk 1454439 FPW
rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
C/E502CF30, prev C/E502AF28, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/18846 blk 1454440 FPW
rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
C/E502EF38, prev C/E502CF30, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/18846 blk 1454441 FPW
rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
C/E5030F40, prev C/E502EF38, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/18846 blk 1454442 FPW
rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
C/E5032F48, prev C/E5030F40, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/18846 blk 1454443 FPW

What is the meaning of FPI_FOR_HINT?

I've replication slots and async replication, but slaves are kept off (PCs
not powered).
No other queries except that one.
Even select count (*) from dati256; gives the problem (sometimes)
Thanks
Pupillo

Re: [GENERAL] Archiving data to another server using copy, psql with pipe

[David G. Johnston]

On Thu, Apr 6, 2017 at 4:24 AM, Moreno Andreo 
wrote:

> psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
> stdout " | psql -h localhost  postgres   -c "copy b from stdin"


​The first question at hand is whether the source psql command will provoke
an EOF (which is the only thing that will stop the copy-from) even though
the complete contents of the copy-to have not yet been sent.

​The second question is whether, even if it does send EOF, the second
command will be allowed to see that EOF and complete its command.  Pipeline
failure mode might impact this (from bash experience).

Unfortunately I do not know the answers to those questions.

The source code might be of some help on the first.

Another option is to replace the first psql process with custom program
that will send data to stdout and in the middle of doing so die with a
non-zero exit code.​  That should be a workable simulation of the copy to
command and evaluation of the target can be done to see what behavior is
exhibitied.

David J.

Re: [GENERAL] A change in the Debian install

[Magnus Hagander]

On Thu, Apr 6, 2017 at 3:46 PM, Stephen Frost  wrote:

> Tom,
>
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > (But ... these statements are based on an assumption of out-of-the-
> > box Postgres behavior.  I would not exactly put it past the Debian
> > packagers to have decided to change this for reasons of their own,
> > and their track record of telling us about such decisions is many
> > miles south of abysmal.  So you might look at whatever patches
> > are in the Debian package to see if there's anything touching
> > pgstat.c's socket-setup logic.)
>
> I don't believe this is really a fair assessment.  Maybe at some point
> in the distant past, but not today.  Christoph is regularly on this list
> contributing to threads regarding packaging, submitting patches of his
> own for improvements to PG, and the patches currently included in the
> Debian distribution, at least mostly, are for things which really should
> be possible to do with configure options, but which we don't provide
> today, or things we should just be handling already.
>

+1. While this may have been true in a *very* distant past, it's certainly
not anymore. So let's try to avoid spreading disinformation about that.

And FWIW, the RPM distributions have about the same number of patches...



> 51-default-sockets-in-var.patch
>   Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR.  We really
>   should allow this to be changed in configure.
>

This looks exactly like something the RPMs want as well, so we should
definitely look at providing that upstream.

I'll start a discussion with Christoph on if we might, already, be able
> to remove some of these, and where we might be able to make upstream
> changes to remove the need for others.
>

That'd be useful. I think you should also include Devrim to figure out what
things would actually make *both* sides happier.

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

Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)

[Scott Marlowe]

On Thu, Apr 6, 2017 at 4:27 AM, Ertan Küçükoğlu
 wrote:
> Sorry for top posting.
>
> I have a serial in master table because I need to know data insertion order.
> DateTime on Raspberry Pi is not accurate due to power loss and lack of
> internet access to fetch correct time from.

Note that serial / sequences do NOT guarantee exact order of
insertion. It's quite possible to have a few switched here and there.
It gets you an approximate order of insertion but a few values could
get swapped around depending on timing of your application and how you
do inserts. Just an FYI.


-- 
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] Archiving data to another server using copy, psql with pipe

[Moreno Andreo]

Il 06/04/2017 16:10, pinker ha scritto:


Error message says, as one could expect, that the second table has got smaller 
precision...
The question isn't about this particular error - which was induced for purpose 
- but about atomicity of this operation
Sorry, I read your message without paying the right attention and I 
totally lost the table structure, plus the answer about transactional 
safety.
Honestly, I can't help you on theory side, I'm sorry. I've never gone so 
far with transactions.
On pratcal side, instead, I can say that rubyrep (a replication system 
written in Ruby) uses quite this kind of approach in its replication 
process (extremely simplified below):

- open transaction
- read from db 1
- write to db 2
- close transaction
When the process encounters some problems (say, connection issues or 
table structure mismatch) an exception is thrown, the transaction is 
rolled back and everyting is as before it started.
I have this process running widely for about 4 years on some hundred 
machines and I've never been reported of transactional problems.


HTH
Moreno.





--
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] Advise on primary key for detail tables (OS: Raspberry Pi)

[Adrian Klaver]

On 04/06/2017 06:40 AM, Ertan Küçükoğlu wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, April 6, 2017 4:18 PM
To: Ertan Küçükoğlu ;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
Raspberry Pi)


On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote:

Sorry for top posting.

I have a serial in master table because I need to know data insertion

order.

DateTime on Raspberry Pi is not accurate due to power loss and lack of
internet access to fetch correct time from.


Understand, though it does beg the question, why have the uuid column?

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


Programmatically it is easier as I can start saving from very bottom detail
table instead of having to save first the master table record. Users will
directly work on detail tables. Master table will be saved & updated by
software automatically as last piece of information.


Hmm. Well back to your original question, PK's on the detail serial 
columns. Given you have a uuid column to mark uniqueness the PK on the 
serial column is redundant. The issue is that database and software 
above it will not 'know' that the uuid column is your PK stand in. 
Putting a UNIQUE index or PK on the uuid column would help flag the 
column as unique. Whether the index is used in a given query is 
dependent on the scope of the query. At some point it is faster for 
Postgres to just scan the table rather then incur the cost of a look up 
on the index.




--
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] Archiving data to another server using copy, psql with pipe

[pinker]

W dniu 2017-04-06 14:28:04 użytkownik Moreno Andreo  
napisał:
> Il 06/04/2017 13:58, pinker ha scritto:
> >
> > W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo 
> >  napisał:
> >> Il 05/04/2017 23:26, pinker ha scritto:
> >>> Hi,
> >>> I'm trying to write an archive manager which will be first copying data 
> >>> from
> >>> tables with where clause and then, after successful load into second 
> >>> server
> >>> - delete them.
> >>> The simplest (and probably fastest) solution I came up with is to use 
> >>> copy:
> >>> psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) 
> >>> to
> >>> stdout " | psql -h localhost  postgres   -c "copy b from stdin"
> >> both  psql -h are on localhost. Is it a typo?
> > No, It's not a typo, just a test ;)

> ... so source and destination database are the same? (just guessing...)
yes, they are as you can easily read - it's postgres in both cases.
This is just easy to reproduce example.

> >
> >>> I have made very simple test to check if I can be sure about 
> >>> "transactional"
> >>> safety. It's not two phase commit of course but it's seems to throw an 
> >>> error
> >>> if something went wrong and it's atomic (i assume). The test was:
> >>>
> >>> CREATE TABLE public.a
> >>> (
> >>> id integer,
> >>> k01 numeric (3)
> >>> );
> >>>
> >>> CREATE TABLE public.b
> >>> (
> >>> id integer,
> >>> k01 numeric (1)
> >>> );
> >>>
> >>> insert into a select n,n from generate_series(1,100) n;
> >>>
> >>> and then:
> >>> psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
> >>> postgres   -c "copy b from stdin"
> >>>
> >>> so psql has thrown an error
> >> ... and what is the error?
> >>> and no rows were inserted to the b table - so it
> >>> seems to be ok.
> >>>
> >>> Is there maybe something I'm missing?
> >>> Some specific condition when something could go wrong and make the process
> >>> not atomic? (i don't care about data consistency in this particular case).
> >> Without knowing OS and psql version of both servers, how they are
> >> connected, or what error you get, it's hard for me to help you further.
> > psql in version 9.6 and OS: Red Hat 7
> > Does Os version really make any difference?
> AFAIK the biggest differences are among different OS families, say 
> Windows and Linux, but there could be some small things among linux 
> distributions. More depth about this topic is beyond my knowledge.
> ... but you did not report the error message, with this is much easier 
> to help you without guessing too much :-)

Error message says, as one could expect, that the second table has got smaller 
precision...
The question isn't about this particular error - which was induced for purpose 
- but about atomicity of this operation

> >
> > Best regards,
> > A. Kucharczyk
> >
> >
> >> Best regards
> >> Moreno.
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context: 
> >>> http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.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
> >>
> >
> >
> >
> 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 





-- 
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] A change in the Debian install

[Stephen Frost]

Tom,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> (But ... these statements are based on an assumption of out-of-the-
> box Postgres behavior.  I would not exactly put it past the Debian
> packagers to have decided to change this for reasons of their own,
> and their track record of telling us about such decisions is many
> miles south of abysmal.  So you might look at whatever patches
> are in the Debian package to see if there's anything touching
> pgstat.c's socket-setup logic.)

I don't believe this is really a fair assessment.  Maybe at some point
in the distant past, but not today.  Christoph is regularly on this list
contributing to threads regarding packaging, submitting patches of his
own for improvements to PG, and the patches currently included in the
Debian distribution, at least mostly, are for things which really should
be possible to do with configure options, but which we don't provide
today, or things we should just be handling already.

The non-comment/documentation patches include for the Debian PG 9.6
packages are:

50-per-version-dirs.patch
  Use version specific installation directories so that several major
  versions can be installed in parallel.  This includes changing
  pkglibdir and includedir_server.  Those might be able to be set
  through existing configure flags and that's probably something we
  could work with Christoph to do.  There's also a change to pg_config
  which might be a bit more difficult to handle in upstream (related to
  how pg_config ends up in /usr/bin, but that isn't the "right" BINDIR).

51-default-sockets-in-var.patch
  Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR.  We really
  should allow this to be changed in configure.

54-debian-alternatives-for-external-tools.patch
  Use 'sensible-editor' for DEFAULT_EDITOR, and 'pager' for
  DEFAULT_PAGER.  These could also be done through configure switches, I
  would think.

64-pg_upgrade-sockdir
  This is a bit of a curious one, the description is:
  Fix for: connection to database failed: Unix-domain socket path
  
"/build/buildd-postgresql-9.3_9.3~beta1-1-i386-mHjRUH/postgresql-9.3-9.3~beta1/build/contrib/pg_upgrade/.s.PGSQL.50432"
  is too long (maximum 107 bytes)

  See also: http://lists.debian.org/debian-wb-team/2013/05/msg00015.html

  This basically adds a mechanism to fall back to using /tmp if the
  socket path is too long.  Would probably be good to figure out a
  better way.

filter-debug-prefix-map
  Description: Remove -fdebug-prefix-map=/p/w/d=. from CFLAGS and
  CONFIGURE in pg_config. The embedded path makes the build
  non-reproducible, and the flag is useless for building extensions
  anyway.
  Not sure there's much we can do about this one, but it's also just for
  pg_config.

hurd-sem_init
  Adds -pthread to LIBS and forces POSIX semaphores on hurd.  I'm not
  sure if this is all still required, but if so, it'd probably be ideal
  if we could figure out a way to handle it automatically instead of
  making the Debian packagers have to do it.

In short, there's certainly nothing that touches pgstat.c's socket-setup
logic and almost all of the changes are just changes to #define's to
specify alternative paths or external tools.

I'll start a discussion with Christoph on if we might, already, be able
to remove some of these, and where we might be able to make upstream
changes to remove the need for others.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)

[Ertan Küçükoğlu]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Thursday, April 6, 2017 4:18 PM
To: Ertan Küçükoğlu ;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
Raspberry Pi)

> On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote:
> > Sorry for top posting.
> >
> > I have a serial in master table because I need to know data insertion
order.
> > DateTime on Raspberry Pi is not accurate due to power loss and lack of 
> > internet access to fetch correct time from.
>
> Understand, though it does beg the question, why have the uuid column?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Programmatically it is easier as I can start saving from very bottom detail
table instead of having to save first the master table record. Users will
directly work on detail tables. Master table will be saved & updated by
software automatically as last piece of information.



-- 
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] Advise on primary key for detail tables (OS: Raspberry Pi)

[Adrian Klaver]

On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote:

Sorry for top posting.

I have a serial in master table because I need to know data insertion order.
DateTime on Raspberry Pi is not accurate due to power loss and lack of
internet access to fetch correct time from.


Understand, though it does beg the question, why have the uuid column?



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, April 5, 2017 7:17 PM
To: Ertan Küçükoğlu ;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
Raspberry Pi)

On 04/05/2017 08:04 AM, Ertan Küçükoğlu wrote:

Hello,

I have a project which will be mainly built on Raspberry Pi and some
parts on Windows.

I must have a PostgreSQL 9.4.10 running on Raspberry Pi and another
PostgreSQL running on Windows. Though, there is still a possibility
that Windows database server will be something else that is not known to

me, yet.

Since Raspberry Pi is running on a SD Card, data saved on Raspberry Pi
will be copied over to Windows database system for a proper backup &
disaster recovery.

I need to keep database server overhead as low as possible on
Raspberry Pi system. That is because software that will be a running
is going to do some time essential sensor communication.

I am about to start table designs on Raspberry Pi. There is one
master-detail-detail-detail structure I should implement. Master
having serial, uuid and some varchar fields. Uuid field being primary
key. Details have serial, uuid and some smallint fields.


So what the serial column in the master table for?



I recall that it is "generally" advised to have a primary key on any
table used on a database server.



What is advised is to have some way of determining uniqueness for a row.
A PK is the simplest way of doing that, also many ORMs will not work without
one. Now a PK can be a single value such as the serial column in your
details tables or it can be over multiple columns that determine uniqueness.
Again you have to be aware of what the application/interface that is using
the tables is capable of. In the case of ORMs, they often do not understand
multi--column PKs. This is why PKs on a
auto-incrementing(serial) integer are often recommended.


My question is: Is reading performance will be faster, if I remove
primary key on serial fields of detail tables and use a regular index
put on master table link fields only? In another words, is it
advisable *not* to have a primary key on PostgreSQL table?

If answer changes according to OS underlying, I appreciate replies
indicates so.

Thanks & regards,
Ertan Küçükoğlu






--
Adrian Klaver
adrian.kla...@aklaver.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] Archiving data to another server using copy, psql with pipe

[Moreno Andreo]

Il 06/04/2017 13:58, pinker ha scritto:


W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo  
napisał:

Il 05/04/2017 23:26, pinker ha scritto:

Hi,
I'm trying to write an archive manager which will be first copying data from
tables with where clause and then, after successful load into second server
- delete them.
The simplest (and probably fastest) solution I came up with is to use copy:
psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
stdout " | psql -h localhost  postgres   -c "copy b from stdin"

both  psql -h are on localhost. Is it a typo?

No, It's not a typo, just a test ;)

... so source and destination database are the same? (just guessing...)



I have made very simple test to check if I can be sure about "transactional"
safety. It's not two phase commit of course but it's seems to throw an error
if something went wrong and it's atomic (i assume). The test was:

CREATE TABLE public.a
(
id integer,
k01 numeric (3)
);

CREATE TABLE public.b
(
id integer,
k01 numeric (1)
);

insert into a select n,n from generate_series(1,100) n;

and then:
psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
postgres   -c "copy b from stdin"

so psql has thrown an error

... and what is the error?

and no rows were inserted to the b table - so it
seems to be ok.

Is there maybe something I'm missing?
Some specific condition when something could go wrong and make the process
not atomic? (i don't care about data consistency in this particular case).

Without knowing OS and psql version of both servers, how they are
connected, or what error you get, it's hard for me to help you further.

psql in version 9.6 and OS: Red Hat 7
Does Os version really make any difference?
AFAIK the biggest differences are among different OS families, say 
Windows and Linux, but there could be some small things among linux 
distributions. More depth about this topic is beyond my knowledge.
... but you did not report the error message, with this is much easier 
to help you without guessing too much :-)


Best regards,
A. Kucharczyk



Best regards
Moreno.




--
View this message in context: 
http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.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










--
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] A change in the Debian install

[Karsten Hilbert]

On Thu, Apr 06, 2017 at 12:05:51AM -0400, Tom Lane wrote:

> rob stone  writes:
> > Upgraded to version 9.6.2-2 and these are the log entries on start-up:-
> 
> > 2017-04-05 08:03:29 AESTLOG:  test message did not get through on
> > socket for statistics collector

...

> (But ... these statements are based on an assumption of out-of-the-
> box Postgres behavior.  I would not exactly put it past the Debian
> packagers to have decided to change this for reasons of their own,
> and their track record of telling us about such decisions is many
> miles south of abysmal.  So you might look at whatever patches
> are in the Debian package to see if there's anything touching
> pgstat.c's socket-setup logic.)

PG 9.6 works just fine for me on mostly-Stable and Testing++
Debian systems.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Archiving data to another server using copy, psql with pipe

[pinker]

W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo  
napisał:
> Il 05/04/2017 23:26, pinker ha scritto:
> > Hi,
> > I'm trying to write an archive manager which will be first copying data from
> > tables with where clause and then, after successful load into second server
> > - delete them.
> > The simplest (and probably fastest) solution I came up with is to use copy:
> > psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
> > stdout " | psql -h localhost  postgres   -c "copy b from stdin"
> both  psql -h are on localhost. Is it a typo?

No, It's not a typo, just a test ;)

> >
> > I have made very simple test to check if I can be sure about "transactional"
> > safety. It's not two phase commit of course but it's seems to throw an error
> > if something went wrong and it's atomic (i assume). The test was:
> >
> > CREATE TABLE public.a
> > (
> >id integer,
> >k01 numeric (3)
> > );
> >
> > CREATE TABLE public.b
> > (
> >id integer,
> >k01 numeric (1)
> > );
> >
> > insert into a select n,n from generate_series(1,100) n;
> >
> > and then:
> > psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
> > postgres   -c "copy b from stdin"
> >
> > so psql has thrown an error
> ... and what is the error?
> > and no rows were inserted to the b table - so it
> > seems to be ok.
> >
> > Is there maybe something I'm missing?
> > Some specific condition when something could go wrong and make the process
> > not atomic? (i don't care about data consistency in this particular case).
> Without knowing OS and psql version of both servers, how they are 
> connected, or what error you get, it's hard for me to help you further.

psql in version 9.6 and OS: Red Hat 7
Does Os version really make any difference?

Best regards,
A. Kucharczyk


> 
> Best regards
> Moreno.
> >
> >
> >
> >
> > --
> > View this message in context: 
> > http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.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
> 





-- 
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] Archiving data to another server using copy, psql with pipe

[Moreno Andreo]

Il 05/04/2017 23:26, pinker ha scritto:

Hi,
I'm trying to write an archive manager which will be first copying data from
tables with where clause and then, after successful load into second server
- delete them.
The simplest (and probably fastest) solution I came up with is to use copy:
psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to
stdout " | psql -h localhost  postgres   -c "copy b from stdin"

both  psql -h are on localhost. Is it a typo?


I have made very simple test to check if I can be sure about "transactional"
safety. It's not two phase commit of course but it's seems to throw an error
if something went wrong and it's atomic (i assume). The test was:

CREATE TABLE public.a
(
   id integer,
   k01 numeric (3)
);

CREATE TABLE public.b
(
   id integer,
   k01 numeric (1)
);

insert into a select n,n from generate_series(1,100) n;

and then:
psql -h localhost postgres -c "copy a to stdout "|psql -h localhost
postgres   -c "copy b from stdin"

so psql has thrown an error

... and what is the error?

and no rows were inserted to the b table - so it
seems to be ok.

Is there maybe something I'm missing?
Some specific condition when something could go wrong and make the process
not atomic? (i don't care about data consistency in this particular case).
Without knowing OS and psql version of both servers, how they are 
connected, or what error you get, it's hard for me to help you further.


Best regards
Moreno.





--
View this message in context: 
http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.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] Advise on primary key for detail tables (OS: Raspberry Pi)

[Ertan Küçükoğlu]

Sorry for top posting.

I have a serial in master table because I need to know data insertion order.
DateTime on Raspberry Pi is not accurate due to power loss and lack of
internet access to fetch correct time from.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, April 5, 2017 7:17 PM
To: Ertan Küçükoğlu ;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Advise on primary key for detail tables (OS:
Raspberry Pi)

On 04/05/2017 08:04 AM, Ertan Küçükoğlu wrote:
> Hello,
>
> I have a project which will be mainly built on Raspberry Pi and some 
> parts on Windows.
>
> I must have a PostgreSQL 9.4.10 running on Raspberry Pi and another 
> PostgreSQL running on Windows. Though, there is still a possibility 
> that Windows database server will be something else that is not known to
me, yet.
> Since Raspberry Pi is running on a SD Card, data saved on Raspberry Pi 
> will be copied over to Windows database system for a proper backup & 
> disaster recovery.
>
> I need to keep database server overhead as low as possible on 
> Raspberry Pi system. That is because software that will be a running 
> is going to do some time essential sensor communication.
>
> I am about to start table designs on Raspberry Pi. There is one 
> master-detail-detail-detail structure I should implement. Master 
> having serial, uuid and some varchar fields. Uuid field being primary 
> key. Details have serial, uuid and some smallint fields.

So what the serial column in the master table for?

>
> I recall that it is "generally" advised to have a primary key on any 
> table used on a database server.
>

What is advised is to have some way of determining uniqueness for a row. 
A PK is the simplest way of doing that, also many ORMs will not work without
one. Now a PK can be a single value such as the serial column in your
details tables or it can be over multiple columns that determine uniqueness.
Again you have to be aware of what the application/interface that is using
the tables is capable of. In the case of ORMs, they often do not understand
multi--column PKs. This is why PKs on a
auto-incrementing(serial) integer are often recommended.

> My question is: Is reading performance will be faster, if I remove 
> primary key on serial fields of detail tables and use a regular index 
> put on master table link fields only? In another words, is it 
> advisable *not* to have a primary key on PostgreSQL table?
>
> If answer changes according to OS underlying, I appreciate replies 
> indicates so.
>
> Thanks & regards,
> Ertan Küçükoğlu
>
>
>


--
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