Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Maxim Boguk]

On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum  wrote:

> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views
> or functions?
>
> That way I could create read only users on a website and limit their
> access to the bare minimum.
>
> Thanks in advance for any advise on this
>
> Alex
>


​Hi,

For functions it's possible (read about SECURITY DEFINER), for view no it
isn't possible (view is query text stored in database for future use and
nothing more).
​



-- 
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ 

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[David G. Johnston]

On Thursday, September 24, 2015, Maxim Boguk  wrote:

>
>
> On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum  > wrote:
>
>> Hi,
>> is it possible to grant select to views and functions without the need to
>> also grant the user the SELECT privileges to the Tables used in the views
>> or functions?
>>
>> That way I could create read only users on a website and limit their
>> access to the bare minimum.
>>
>>

> for view no it isn't possible (view is query text stored in database for
> future use and nothing more).
>
>
This is not how views are stored nor how they work.  The are implemented as
rules and thus the following section of the documentation applies.

 http://www.postgresql.org/docs/9.4/interactive/rules-privileges.html

David J.

Fwd: [GENERAL] Convert number to string

[Francisco Olarte]

Forgot replying to all, forwarding to the list, sorree for the potential dupes.


Hi Hengky:

On Thu, Sep 24, 2015 at 10:09 AM, Hengky Liwandouw
 wrote:
>
> I don’t have any idea how can I use postgres function to convert series  of 
> number (currency) to my own string.
>
>
>
> My string : F G H I J K L M N  as the replacement for number : 1 2 3 
> 4 5 6 7 8 9
>
> Dozens = O
>
> Hundreds = P
>
> Thousands = C
>
> Ten thousands = B
>
>
>
> So…
>
> 200 = GP
>
> 2000 = GC
>
> 1150 = FFJO
>
> 3 = HB
>
>
>
> Any idea ?


Your example es severely unspecified, how do you translate 1024? and
1002? and 10?

given the numbers in the example you can use a simple trick. 1st
replace using O for 0 via translate ( I'm not using capitals in teh
exmaple for better 0-o contrast ):

# with data(s) as (values('200'),('2000'),('1150'),('3')) select
translate(s,'0123456789','ofghijklmn') from data;
 translate
---
 goo
 gooo
 ffjo
 h
(4 rows)

then replace sequences of 'o' starting with the longer ones:

# with source(s) as (values('200'),('2000'),('1150'),('3'))
, step1(s) as (select translate(s,'0123456789','ofghijklmn') from source)
, step2(s) as (select replace(s,'','b') from step1)
, step3(s) as (select replace(s,'ooo','c') from step2)
, step4(s) as (select replace(s,'oo','p') from step3)
select * from step4;
  s
--
 gp
 gc
 ffjo
 hb
(4 rows)

clasical trick. But, as I said above, you need to specify it much better.

Francisco Olarte.


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

[GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Alex Magnum]

Hi,
is it possible to grant select to views and functions without the need to
also grant the user the SELECT privileges to the Tables used in the views
or functions?

That way I could create read only users on a website and limit their access
to the bare minimum.

Thanks in advance for any advise on this

Alex

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[David G. Johnston]

On Thursday, September 24, 2015, Alex Magnum  wrote:

> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views
> or functions?
>
> That way I could create read only users on a website and limit their
> access to the bare minimum.
>
> Thanks in advance for any advise on this
>
> Alex
>

Views work this way by default.  You can specify "security definer" to get
similar behavior when you create a function.

David J.

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Thom Brown]

On 24 September 2015 at 12:28, Alex Magnum  wrote:
> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views or
> functions?
>
> That way I could create read only users on a website and limit their access
> to the bare minimum.
>
> Thanks in advance for any advise on this

Yes.  For views, you just need to provide select access to the user,
but revoke general permissions from the public pseudo role.

Example:

postgres=# create user limited_user;
CREATE ROLE

postgres=# create table mydata (id serial primary key, content text);
CREATE TABLE

postgres=# insert into mydata (content) values ('blue'),('red'),('green');
INSERT 0 3

postgres=# revoke all on mydata from public;
REVOKE

postgres=# create view v_mydata as SELECT content from mydata;
CREATE VIEW

postgres=# grant select on v_mydata to limited_user;
GRANT

postgres=# \c - limited_user
You are now connected to database "postgres" as user "limited_user".

postgres=> select * from mydata;
ERROR:  permission denied for relation mydata

postgres=> select * from v_mydata;
 content
-
 blue
 red
 green
(3 rows)


With functions, you just set them up with the label SECURITY DEFINER.
This means that the function runs as the owner of the function, rather
than whomever is calling it:

postgres=# \c - postgres

postgres=# CREATE or replace FUNCTION get_colour(colour_id int)
returns text as $$
declare
  colour_name text;
begin
  select content into colour_name from mydata where id = colour_id;
  return colour_name;
end; $$ language plpgsql SECURITY DEFINER;

postgres=# revoke all on function get_colour(int) from public;
REVOKE

postgres=# grant execute on function get_colour(int) to limited_user;
GRANT

postgres=# \c - limited_user
You are now connected to database "postgres" as user "limited_user".

postgres=> select get_colour(2);
 get_colour

 red
(1 row)


Thom


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

[GENERAL] Use tar to online backup has an error

[maxiangqian]

Hi,

when I use tar to online backup ,It has an error:


/bin/tar: Removing leading `/' from member names

/bin/tar: /data/test/PG_9.0_201008051/20001/2451: Cannot stat: No such
file or directory

/bin/tar: Error exit delayed from previous errors





and /data/test/PG_9.0_201008051/20001/2451 file is not exist.







[~]$ ls -al   /data/test/PG_9.0_201008051/20001/2451

ls: /data/test/PG_9.0_201008051/20001/2451 : No such file or directory

what should I do to resolve this error.



  I'm using pg9.0.



Thanks

[GENERAL] Convert number to string

[Hengky Liwandouw]

Hi,

 

I don't have any idea how can I use postgres function to convert series  of
number (currency) to my own string. 

 

My string : F G H I J K L M N  as the replacement for number : 1 2 3
4 5 6 7 8 9

Dozens = O

Hundreds = P

Thousands = C

Ten thousands = B

 

So. 

200 = GP

2000 = GC

1150 = FFJO

3 = HB

 

Any idea ?

 

Thanks In advance



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Re: [GENERAL] Convert number to string

[John R Pierce]

On 9/24/2015 1:09 AM, Hengky Liwandouw wrote:


Hi,

I don’t have any idea how can I use postgres function to convert 
series  of number (currency) to my own string.


My string : F G H I J K L M N  as the replacement for number : 
1 2 3 4 5 6 7 8 9


Dozens = O

Hundreds = P

Thousands = C

Ten thousands = B

So…

200 = GP

2000 = GC

1150 = FFJO

3 = HB

Any idea ?



going from FFJO -> 1150 is simple character substitution (assuming 
'dozens' actually means tens, and not its traditional meaning of 12s).


going the other way, thats probably a good excuse for a plperl function.

--
john r pierce, recycling bits in santa cruz

[GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

Hi,

We're upgrading a database from 8.4 to 9.4
The web developer complains that the timestamps are suddenly 2 hours late.
We are in GMT+02.
The issue would go away if we cast the postgres timestamps to timestamp
WITH timezone. It works in pg8.4 and 9.4

He told me that PHP always uses timezones, so i tried to reproduce it
without the application layer.
Since PHP always uses a timezone, the first part of the query always
converts to "with time zone', it is what i presume PHP is doing.

select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3)
--> 02:00:009.4
--> 00:00:008.4

select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp  WITH TIME ZONE) * interval '1 second' -
now(),substr(version(), 12, 3)
--> 00:00:009.4
--> 00:00:008.4

Is there a reason for this change of behavior between 8.4 and 9.* ?


Cheers,
-- 
Willy-Bas Loos

Re: [GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

On Thu, Sep 24, 2015 at 4:01 PM, Willy-Bas Loos  wrote:

> =# show timezone;
>  TimeZone
> ---
>  localtime
> (1 row)
>
>
>
sorry for the top post
-- 
Willy-Bas Loos

Re: [GENERAL] Convert number to string

[Francisco Olarte]

HI Melvin:

On Thu, Sep 24, 2015 at 2:51 PM, Melvin Davidson 
wrote:

> Postgresql has a translate function:
>

​Did you read the ( quoted at the bottom of the reply you sent ) message
you were replying to? It contained a working sample using exactly this
translate.​ ;->

>
> You would then need a little further processing to determine the tens,
> hundreds, etc.
>
> I'll leave that to you to work out, but additional functions of
> strpos(string, substring)
> substr(string, from [, count])
> length(string)
>

​Do not forget replace, shorter, easier.

Francisco Olarte.​

Re: [GENERAL] epoch and timezone changed bevior

[Willy-Bas Loos]

=# show timezone;
 TimeZone
---
 localtime
(1 row)


On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver 
wrote:

> On 09/24/2015 06:42 AM, Willy-Bas Loos wrote:
>
>> Hi,
>>
>> We're upgrading a database from 8.4 to 9.4
>> The web developer complains that the timestamps are suddenly 2 hours
>> late. We are in GMT+02.
>> The issue would go away if we cast the postgres timestamps to timestamp
>> WITH timezone. It works in pg8.4 and 9.4
>>
>> He told me that PHP always uses timezones, so i tried to reproduce it
>> without the application layer.
>> Since PHP always uses a timezone, the first part of the query always
>> converts to "with time zone', it is what i presume PHP is doing.
>>
>
> That is the same as assuming and I would verify.
>
>
>> select timestamp with time zone 'epoch' + extract(epoch from
>> now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3)
>> --> 02:00:009.4
>> --> 00:00:008.4
>>
>> select timestamp with time zone 'epoch' + extract(epoch from
>> now()::timestamp  WITH TIME ZONE) * interval '1 second' -
>> now(),substr(version(), 12, 3)
>> --> 00:00:009.4
>> --> 00:00:008.4
>>
>
> What does:
>
> show timezone;
>
> return?
>
>
>> Is there a reason for this change of behavior between 8.4 and 9.* ?
>>
>
> Have you looked at what TimeZone is set to in the 8.4 and 9.4
> postgresql.conf files?
>
> The method of setting that during initdb changed in 9.2:
>
> http://www.postgresql.org/docs/9.4/interactive/release-9-2.html
>
> E.29.3.1.7.1. postgresql.conf
>
> Identify the server time zone during initdb, and set postgresql.conf
> entries timezone and log_timezone accordingly (Tom Lane)
>
> This avoids expensive time zone probes during server start.
>
>>
>>
>> Cheers,
>> --
>> Willy-Bas Loos
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Willy-Bas Loos

Re: [GENERAL] Convert number to string

[Melvin Davidson]

Postgresql has a translate function:

So you can create a pgsql function that
A;: converts the numeric to text:   txtnum := 12345::text
B, Translate it. eg: transnum := SELECT translate(txtnum, '123456789',
'FGHIJKLMN');
eg SELECT translate('31241', '12345', 'FGHIJ'); = HFGIF

You would then need a little further processing to determine the tens,
hundreds, etc.

I'll leave that to you to work out, but additional functions of
strpos(string, substring)
substr(string, from [, count])
length(string)


would seem helpful
http://www.postgresql.org/docs/9.2/interactive/functions-string.html


On Thu, Sep 24, 2015 at 5:35 AM, Francisco Olarte 
wrote:

> Forgot replying to all, forwarding to the list, sorree for the potential
> dupes.
>
>
> Hi Hengky:
>
> On Thu, Sep 24, 2015 at 10:09 AM, Hengky Liwandouw
>  wrote:
> >
> > I don’t have any idea how can I use postgres function to convert series
> of number (currency) to my own string.
> >
> >
> >
> > My string : F G H I J K L M N  as the replacement for number : 1
> 2 3 4 5 6 7 8 9
> >
> > Dozens = O
> >
> > Hundreds = P
> >
> > Thousands = C
> >
> > Ten thousands = B
> >
> >
> >
> > So…
> >
> > 200 = GP
> >
> > 2000 = GC
> >
> > 1150 = FFJO
> >
> > 3 = HB
> >
> >
> >
> > Any idea ?
>
>
> Your example es severely unspecified, how do you translate 1024? and
> 1002? and 10?
>
> given the numbers in the example you can use a simple trick. 1st
> replace using O for 0 via translate ( I'm not using capitals in teh
> exmaple for better 0-o contrast ):
>
> # with data(s) as (values('200'),('2000'),('1150'),('3')) select
> translate(s,'0123456789','ofghijklmn') from data;
>  translate
> ---
>  goo
>  gooo
>  ffjo
>  h
> (4 rows)
>
> then replace sequences of 'o' starting with the longer ones:
>
> # with source(s) as (values('200'),('2000'),('1150'),('3'))
> , step1(s) as (select translate(s,'0123456789','ofghijklmn') from source)
> , step2(s) as (select replace(s,'','b') from step1)
> , step3(s) as (select replace(s,'ooo','c') from step2)
> , step4(s) as (select replace(s,'oo','p') from step3)
> select * from step4;
>   s
> --
>  gp
>  gc
>  ffjo
>  hb
> (4 rows)
>
> clasical trick. But, as I said above, you need to specify it much better.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Use tar to online backup has an error

[Yves Dorfsman]

On 2015-09-24 03:55, maxiangqian wrote:
 
> when I use tar to online backup ,It has an error:
> 
> /bin/tar: Removing leading `/' from member names

This is not an error, more like a warning. GNU tar doesn't not store the full 
path in the tar file, but a relative path, as full paths are considered 
dangerous (easier to overwrite existing data by mistake).


> /bin/tar: /data/test/PG_9.0_201008051/20001/2451: Cannot stat: No such
> file or directory
> 
> /bin/tar: Error exit delayed from previous errors
> 
> 
> and /data/test/PG_9.0_201008051/20001/2451 file is not exist.
> 

It looks like you are trying to make a filesystem backup while the files are 
still being updated. In order to make a filesystem backup you need to either:

  - shutdown the database
  - run "pg_start_backup" (make SURE that you run pg_stop_backup" when done)

http://www.postgresql.org/docs/9.3/static/backup-file.html
http://www.postgresql.org/docs/9.3/static/continuous-archiving.html#BACKUP-TIPS


-- 
http://yves.zioup.com
gpg: 4096R/32B0F416 



-- 
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] epoch and timezone changed bevior

[Vik Fearing]

On 09/24/2015 03:42 PM, Willy-Bas Loos wrote:
> Hi,
> 
> We're upgrading a database from 8.4 to 9.4
> The web developer complains that the timestamps are suddenly 2 hours late.
> We are in GMT+02.
> The issue would go away if we cast the postgres timestamps to timestamp
> WITH timezone. It works in pg8.4 and 9.4
> 
> He told me that PHP always uses timezones, so i tried to reproduce it
> without the application layer.
> Since PHP always uses a timezone, the first part of the query always
> converts to "with time zone', it is what i presume PHP is doing.
> 
> select timestamp with time zone 'epoch' + extract(epoch from
> now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3)
> --> 02:00:009.4
> --> 00:00:008.4
> 
> select timestamp with time zone 'epoch' + extract(epoch from
> now()::timestamp  WITH TIME ZONE) * interval '1 second' -
> now(),substr(version(), 12, 3)
> --> 00:00:009.4
> --> 00:00:008.4
> 
> Is there a reason for this change of behavior between 8.4 and 9.* ?

Yes. As of 9.2, the server's timezone is set when the database is
initialized. See the following commit message:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ca4af308c32d03db5fbacb54d6e583ceb904f268
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] epoch and timezone changed bevior

[Adrian Klaver]

On 09/24/2015 06:42 AM, Willy-Bas Loos wrote:

Hi,

We're upgrading a database from 8.4 to 9.4
The web developer complains that the timestamps are suddenly 2 hours
late. We are in GMT+02.
The issue would go away if we cast the postgres timestamps to timestamp
WITH timezone. It works in pg8.4 and 9.4

He told me that PHP always uses timezones, so i tried to reproduce it
without the application layer.
Since PHP always uses a timezone, the first part of the query always
converts to "with time zone', it is what i presume PHP is doing.


That is the same as assuming and I would verify.



select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp) * interval '1 second'-now(),substr(version(), 12, 3)
--> 02:00:009.4
--> 00:00:008.4

select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp  WITH TIME ZONE) * interval '1 second' -
now(),substr(version(), 12, 3)
--> 00:00:009.4
--> 00:00:008.4


What does:

show timezone;

return?



Is there a reason for this change of behavior between 8.4 and 9.* ?


Have you looked at what TimeZone is set to in the 8.4 and 9.4 
postgresql.conf files?


The method of setting that during initdb changed in 9.2:

http://www.postgresql.org/docs/9.4/interactive/release-9-2.html

E.29.3.1.7.1. postgresql.conf

Identify the server time zone during initdb, and set postgresql.conf 
entries timezone and log_timezone accordingly (Tom Lane)


This avoids expensive time zone probes during server start.



Cheers,
--
Willy-Bas Loos



--
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] Use tar to online backup has an error

[David Steele]

On 9/24/15 9:29 AM, Yves Dorfsman wrote:

On 2015-09-24 03:55, maxiangqian wrote:

/bin/tar: /data/test/PG_9.0_201008051/20001/2451: Cannot stat: No such
file or directory

/bin/tar: Error exit delayed from previous errors


and /data/test/PG_9.0_201008051/20001/2451 file is not exist.



It looks like you are trying to make a filesystem backup while the files are 
still being updated. In order to make a filesystem backup you need to either:

   - shutdown the database
   - run "pg_start_backup" (make SURE that you run pg_stop_backup" when done)

http://www.postgresql.org/docs/9.3/static/backup-file.html
http://www.postgresql.org/docs/9.3/static/continuous-archiving.html#BACKUP-TIPS


It's actually perfectly normal for files to disappear during a backup, 
even when pg_start_backup() is called first (never perform file-level 
backup with calling pg_start_backup()).  The database continues to 
operation normally during a backup so any dropped table (or temp table) 
will result it files being removed by Postgres, not to mention changes 
in clog, pg_xlog, etc.


All of this is reconciled during the replay of WAL after a restore.  You 
just need to ignore any missing file errors while taking the backup and 
be sure to back up all the WAL segments required to make the database 
consistent. pg_basebackup will do all of this for you.


Another option is to use purpose-built backup software such as 
pgBackRest or barman, both of which are open source.


--
-David
da...@pgmasters.net


--
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] epoch and timezone changed bevior

[Tom Lane]

Adrian Klaver  writes:
> On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:
>> =# show timezone;
>> TimeZone
>> ---
>> localtime
>> (1 row)

> This sounded familiar:
> http://www.postgresql.org/message-id/m3616t3m5d@carbon.jhcloos.org

Yeah ... we never did figure out what was producing that setting on
Cloos' machine.  But it's not relevant to the specific problem being
complained of here.

regards, tom lane


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

Re: [GENERAL] Use tar to online backup has an error

[Stephen Frost]

* David Steele (da...@pgmasters.net) wrote:
> It's actually perfectly normal for files to disappear during a
> backup, even when pg_start_backup() is called first (never perform
> file-level backup with calling pg_start_backup()).  The database

*without* calling pg_start_backup, you mean. :)

> All of this is reconciled during the replay of WAL after a restore.

... which is why archive_command must be set and the WAL files must be
archived, otherwise PG can't go back and replay those changes.

> Another option is to use purpose-built backup software such as
> pgBackRest or barman, both of which are open source.

+100.  Rolling your own backup solution for PG really stinks and is
difficult to get right.  Use something which knows about all of the
above, verifies that all the necessary WAL is archived, does all of the
correct fsync's for the WAL files when they're saved, handles failure
gracefully, etc, etc.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Use tar to online backup has an error

[David Steele]

On 9/24/15 10:15 AM, Stephen Frost wrote:

* David Steele (da...@pgmasters.net) wrote:

It's actually perfectly normal for files to disappear during a
backup, even when pg_start_backup() is called first (never perform
file-level backup with calling pg_start_backup()).  The database


*without* calling pg_start_backup, you mean. :)


Whoops!  That's definitely what I meant.

--
-David
da...@pgmasters.net


--
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] epoch and timezone changed bevior

[Tom Lane]

Willy-Bas Loos  writes:
> Is there a reason for this change of behavior between 8.4 and 9.* ?

See the "incompatibilities" section in the 9.2 release notes:

* Make EXTRACT(EPOCH FROM timestamp without time zone) measure the
  epoch from local midnight, not UTC midnight (Tom Lane)

This change reverts an ill-considered change made in release
7.3. Measuring from UTC midnight was inconsistent because it made
the result dependent on the timezone setting, which computations
for timestamp without time zone should not be. The previous
behavior remains available by casting the input value to timestamp
with time zone.


regards, tom lane


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

Re: [GENERAL] Automatically Updatable Foreign Key Views

[Tom Lane]

David Rowley  writes:
> On 24 September 2015 at 13:32, Raymond Brinzer 
> wrote:
>> Any thoughts on this would be welcome.

> The problem is that an UPDATE/DELETE could take place which causes the
> foreign key to be violated and you may try and perform an UPDATE to the
> view before the foreign key is cascaded by the trigger at end of
> statement/transaction. Remember that a statement could execute inside of a
> volatile function being called by some outer query.

Yeah.  We discussed this awhile back in the context of a proposal to
optimize query plans on the assumption that foreign-key constraints hold
(which would allow joins to be removed in some cases).  That proposal was
to only apply the optimization if there were no unfired trigger events in
the current transaction, which would imply that there were no unperformed
foreign key checks.  That's valid as far as it goes, and you could imagine
narrowing the restriction even more by checking to see if there were
specifically any FK triggers queued for the query's table(s).  However the
potential delay between planning and execution made it a real mess to be
sure if the optimization is safe, so I kind of doubt that it'll ever be
accepted.

In this context, using a similar approach would mean that it would be
state-dependent whether an update on a view was allowed at all, which
seems way too messy IMO.  Even worse, if one update was allowed then
the next one would not be, because the update on the view's underlying
table would have queued FK check trigger events.

In fact, I think this means an auto update through the view couldn't be
allowed to update more than one row, because the first row update might
have invalidated the FK constraint thus breaking the assumption needed
for the second update to be well-defined.  That statement is independent
of any particular implementation approach.  There are probably ways around
that, such as not allowing the FK-involved columns to be auto updatable,
but it's really looking like a mess.

regards, tom lane


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

Re: [GENERAL] epoch and timezone changed bevior

[Adrian Klaver]

On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:

=# show timezone;
  TimeZone
---
  localtime
(1 row)



Is this the same on both 8.4 and 9.4?

Are both servers on the same machine?

What does /etc/localtime point to?




On Thu, Sep 24, 2015 at 3:57 PM, Adrian Klaver
> wrote:

On 09/24/2015 06:42 AM, Willy-Bas Loos wrote:

Hi,

We're upgrading a database from 8.4 to 9.4
The web developer complains that the timestamps are suddenly 2 hours
late. We are in GMT+02.
The issue would go away if we cast the postgres timestamps to
timestamp
WITH timezone. It works in pg8.4 and 9.4

He told me that PHP always uses timezones, so i tried to
reproduce it
without the application layer.
Since PHP always uses a timezone, the first part of the query always
converts to "with time zone', it is what i presume PHP is doing.


That is the same as assuming and I would verify.


select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp) * interval '1 second'-now(),substr(version(),
12, 3)
--> 02:00:009.4
--> 00:00:008.4

select timestamp with time zone 'epoch' + extract(epoch from
now()::timestamp  WITH TIME ZONE) * interval '1 second' -
now(),substr(version(), 12, 3)
--> 00:00:009.4
--> 00:00:008.4


What does:

show timezone;

return?


Is there a reason for this change of behavior between 8.4 and 9.* ?


Have you looked at what TimeZone is set to in the 8.4 and 9.4
postgresql.conf files?

The method of setting that during initdb changed in 9.2:

http://www.postgresql.org/docs/9.4/interactive/release-9-2.html

E.29.3.1.7.1. postgresql.conf

Identify the server time zone during initdb, and set postgresql.conf
entries timezone and log_timezone accordingly (Tom Lane)

This avoids expensive time zone probes during server start.



Cheers,
--
Willy-Bas Loos



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




--
Willy-Bas Loos



--
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] epoch and timezone changed bevior

[Adrian Klaver]

On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:

=# show timezone;
  TimeZone
---
  localtime
(1 row)




This sounded familiar:

http://www.postgresql.org/message-id/m3616t3m5d@carbon.jhcloos.org

From there, per Tom Lane:

select * from pg_settings where name = 'TimeZone';

This will show what is actually supplying the timezone value from the 
Postgres side.


Might be easiest to just set timezone in postgresql.conf to what you 
want it to be.


--
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] epoch and timezone changed bevior

[Adrian Klaver]

On 09/24/2015 08:08 AM, Tom Lane wrote:

Adrian Klaver  writes:

On 09/24/2015 07:01 AM, Willy-Bas Loos wrote:

=# show timezone;
TimeZone
---
localtime
(1 row)



This sounded familiar:
http://www.postgresql.org/message-id/m3616t3m5d@carbon.jhcloos.org


Yeah ... we never did figure out what was producing that setting on
Cloos' machine.  But it's not relevant to the specific problem being
complained of here.


Yeah, I forgot about the EXTRACT change.



regards, tom lane




--
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] epoch and timezone changed bevior

[Willy-Bas Loos]

On Thu, Sep 24, 2015 at 5:22 PM, Adrian Klaver 
wrote:


> Yeah, I forgot about the EXTRACT change.
>
>
>> regards, tom lane
>>
>>
>

thanks a lot for clarifying!

-- 
Willy-Bas Loos

[GENERAL] [Q] Serializable

[Ladislav Lenart]

Hello.

I would like to know how SERIALIZABLE behaves.

Suppose I have two (or more) concurrent DB transactions:
* Each runs in SERIALIZABLE.
* Each updates (insert / update / delete) different rows in the same table.

Can I get serializable failures (i.e. ERROR: could not serialize access due to
read/write dependencies among transactions)?

This is on 9.4.

Thank you in advance,

Ladislav Lenart



-- 
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] [Q] Serializable

[David G. Johnston]

On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart 
wrote:

> Hello.
>
> I would like to know how SERIALIZABLE behaves.
>
> Suppose I have two (or more) concurrent DB transactions:
> * Each runs in SERIALIZABLE.
> * Each updates (insert / update / delete) different rows in the same table.
>
> Can I get serializable failures (i.e. ERROR: could not serialize access
> due to
> read/write dependencies among transactions)?
>
> This is on 9.4.
>
> Thank you in advance,
>

​Probably not but there seems to be insufficient information provided to
prove this.  You seem to probably have the "write dependency" covered but
you mention nothing about "read dependencies".

Why not just assume it can and put code in place to handle that possibility
- especially since you should probably be frameworking database access to
enforce that all parts of the system use SERIALIZABLE?

David J.
​

Re: [GENERAL] Dropped connections with pg_basebackup

[Sherrylyn Branchaw]

I'm assuming based on the "SSL error" that you have ssl set to 'on'. What's
your ssl_renegotiation_limit? The default is 512MB, but setting it to 0 has
solved problems for a number of people on this list, including myself.

Sherrylyn

On Thu, Sep 24, 2015 at 3:57 PM, Francisco Reyes  wrote:

> Have an existing setup of 9.3 servers. Replication has been rock solid,
> but recently the circuits between data centers were upgraded and
> pg_basebackup now seems to fail often when setting up streaming
> replication. What used to take 10+ hours now  only took 68 minutes, but had
> to do many retries. Many attempts fail within minutes while others go to
> 90% or higher and then drop. The reason we are doing a sync is because we
> have to swap data centers every so often for compliance. So I had to swap
> master and slave.
>
> Calling pg_basebackup like this:
> pg_basebackup -P -R -X s -h  -D  -U replicator
>
> The error we keep having is:
> Sep 23 13:36:32  postgres[16804]: [11-1] 2015-09-23 13:36:32 EDT
>  [unknown] replicator LOG: SSL error: bad write retry
> Sep 23 13:36:32  postgres[16804]: [12-1] 2015-09-23 13:36:32 EDT
>  [unknown] replicator LOG: SSL error: bad write retry
> Sep 23 13:36:32  postgres[16804]: [13-1] 2015-09-23 13:36:32 EDT
>  [unknown] replicator FATAL: connection to client lost
> Sep 23 13:36:32  postgres[16972]: [9-1] 2015-09-23 13:36:32 EDT
>  [unknown] replicator LOG: could not receive data from client:
> Connection reset by peer
>
> I have been working with the network team and we have even been actively
> monitoring the line, and running ping, as the replication is setup. At the
> point the connection reset by peer error happens, we don't see any issue
> with the network and ping doesn't show an issue at that point in time.
>
> The issue also happened on another set of machines and likewise, had to
> retry many times before pg_basebackup would do the initial sync. Once the
> initial sync is set, replication is fine.
>
> I  tried both "-X s" (stream) and "-X f" (fetch) and both fail often.
>
> Any ideas what may be going on?
>
>
> --
> 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] Dropped connections with pg_basebackup

[Adrian Klaver]

On 09/24/2015 12:57 PM, Francisco Reyes wrote:

Have an existing setup of 9.3 servers. Replication has been rock solid,
but recently the circuits between data centers were upgraded and
pg_basebackup now seems to fail often when setting up streaming
replication. What used to take 10+ hours now  only took 68 minutes, but
had to do many retries. Many attempts fail within minutes while others
go to 90% or higher and then drop. The reason we are doing a sync is
because we have to swap data centers every so often for compliance. So I
had to swap master and slave.

Calling pg_basebackup like this:
pg_basebackup -P -R -X s -h  -D  -U replicator

The error we keep having is:
Sep 23 13:36:32  postgres[16804]: [11-1] 2015-09-23 13:36:32
EDT  [unknown] replicator LOG: SSL error: bad write retry
Sep 23 13:36:32  postgres[16804]: [12-1] 2015-09-23 13:36:32
EDT  [unknown] replicator LOG: SSL error: bad write retry


Seems to be an SSL problem, so how is your SSL set up on the servers?


Sep 23 13:36:32  postgres[16804]: [13-1] 2015-09-23 13:36:32
EDT  [unknown] replicator FATAL: connection to client lost
Sep 23 13:36:32  postgres[16972]: [9-1] 2015-09-23 13:36:32
EDT  [unknown] replicator LOG: could not receive data from client:
Connection reset by peer

I have been working with the network team and we have even been actively
monitoring the line, and running ping, as the replication is setup. At
the point the connection reset by peer error happens, we don't see any
issue with the network and ping doesn't show an issue at that point in
time.

The issue also happened on another set of machines and likewise, had to
retry many times before pg_basebackup would do the initial sync. Once
the initial sync is set, replication is fine.

I  tried both "-X s" (stream) and "-X f" (fetch) and both fail often.

Any ideas what may be going on?





--
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] Dropped connections with pg_basebackup

[Sherrylyn Branchaw]

Ah, yes, it's been removed from 9.5:
http://www.postgresql.org/docs/9.5/static/release-9-5.html

Good to know.

On Thu, Sep 24, 2015 at 4:34 PM, Alvaro Herrera 
wrote:

> Sherrylyn Branchaw wrote:
> > I'm assuming based on the "SSL error" that you have ssl set to 'on'.
> What's
> > your ssl_renegotiation_limit? The default is 512MB, but setting it to 0
> has
> > solved problems for a number of people on this list, including myself.
>
> Moreover, the default has been set to 0, because the bugs both in our
> usage and in OpenSSL code itself seem never to end.  Just disable it.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Use tar to online backup has an error

[maxiangqian]

 Hi

I got it. Thanks for your kindly support!!!

Thank you very much.


2015-09-24 22:18 GMT+08:00 David Steele :

> On 9/24/15 10:15 AM, Stephen Frost wrote:
>
>> * David Steele (da...@pgmasters.net) wrote:
>>
>>> It's actually perfectly normal for files to disappear during a
>>> backup, even when pg_start_backup() is called first (never perform
>>> file-level backup with calling pg_start_backup()).  The database
>>>
>>
>> *without* calling pg_start_backup, you mean. :)
>>
>
> Whoops!  That's definitely what I meant.
>
> --
> -David
> da...@pgmasters.net
>

[GENERAL] Dropped connections with pg_basebackup

[Francisco Reyes]

Have an existing setup of 9.3 servers. Replication has been rock solid, 
but recently the circuits between data centers were upgraded and 
pg_basebackup now seems to fail often when setting up streaming 
replication. What used to take 10+ hours now  only took 68 minutes, but 
had to do many retries. Many attempts fail within minutes while others 
go to 90% or higher and then drop. The reason we are doing a sync is 
because we have to swap data centers every so often for compliance. So I 
had to swap master and slave.


Calling pg_basebackup like this:
pg_basebackup -P -R -X s -h  -D  -U replicator

The error we keep having is:
Sep 23 13:36:32  postgres[16804]: [11-1] 2015-09-23 13:36:32 
EDT  [unknown] replicator LOG: SSL error: bad write retry
Sep 23 13:36:32  postgres[16804]: [12-1] 2015-09-23 13:36:32 
EDT  [unknown] replicator LOG: SSL error: bad write retry
Sep 23 13:36:32  postgres[16804]: [13-1] 2015-09-23 13:36:32 
EDT  [unknown] replicator FATAL: connection to client lost
Sep 23 13:36:32  postgres[16972]: [9-1] 2015-09-23 13:36:32 
EDT  [unknown] replicator LOG: could not receive data from client: 
Connection reset by peer


I have been working with the network team and we have even been actively 
monitoring the line, and running ping, as the replication is setup. At 
the point the connection reset by peer error happens, we don't see any 
issue with the network and ping doesn't show an issue at that point in time.


The issue also happened on another set of machines and likewise, had to 
retry many times before pg_basebackup would do the initial sync. Once 
the initial sync is set, replication is fine.


I  tried both "-X s" (stream) and "-X f" (fetch) and both fail often.

Any ideas what may be going on?


--
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] Dropped connections with pg_basebackup

[Alvaro Herrera]

Sherrylyn Branchaw wrote:
> I'm assuming based on the "SSL error" that you have ssl set to 'on'. What's
> your ssl_renegotiation_limit? The default is 512MB, but setting it to 0 has
> solved problems for a number of people on this list, including myself.

Moreover, the default has been set to 0, because the bugs both in our
usage and in OpenSSL code itself seem never to end.  Just disable it.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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