On Fri, Jan 04, 2019 at 08:41:35PM -0600, Jerry Sievers wrote:
> Take a look at the *.history file in pg_xlog or pg_wal.
These files are also archived. If you want to be able to see such
contents at SQL level, you would need some parsing logic like this one
for example which is a toy of mine (thi
Jeremy Finzel writes:
> I am having a hard time finding out how I can easily determine at
> which LSN a streamer was promoted after promotion. A related
> question is that if I pause wal replay, I am able to see the last
> replayed LSN but I can't simply immediately promote.
Take a look at the
I am having a hard time finding out how I can easily determine at which LSN
a streamer was promoted *after *promotion. A related question is that if I
pause wal replay, I am able to see the last replayed LSN but I can't simply
immediately promote.
I want to know the state of the streamer post-pro
> "Ken" == Ken Tanzer writes:
>> If you absolutely can't change the column type, then one option
>> would be to do your own fixed-format date parsing function (and
>> label it immutable), e.g.
>>
>> create function iso_timestamp(text)
>> returns timestamp without time zone
>> as $$ se
Allison Kaptur writes:
> I encountered a surprising error when writing a migration that both added a
> primary key to a table and added a new NOT NULL column. It threw the error "
> column "col_d" contains null values", even though I supplied a default. The
> migration looks like this:
> CREATE TA
Hi folks,
I encountered a surprising error when writing a migration that both added a
primary key to a table and added a new NOT NULL column. It threw the error "
column "col_d" contains null values", even though I supplied a default. The
migration looks like this:
CREATE TABLE new_table AS SELECT
On Fri, 4 Jan 2019, Jeremy Finzel wrote:
Another suggestion which hasn’t been mentioned is using ‘infinity’ as the
end date. I like this because it IMO indicates that the record is clearly
the current valid record more than null.
Jeremy,
I believe that infinity was mentioned in this thread.
On Fri, Jan 4, 2019 at 4:19 PM Rich Shepard
wrote:
> On Fri, 4 Jan 2019, David G. Johnston wrote:
>
> > That would be the decision to make - does your toolkit support (or can be
> > made to support) the type and are you willing to choose a sub-optimal
> > database model because one or more applic
From: Ken Tanzer
On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth
mailto:and...@tao11.riddles.org.uk>> wrote:
> "Ken" == Ken Tanzer mailto:ken.tan...@gmail.com>>
> writes:
Ken> Hi. I've got a text field in a table that holds this style of
Ken> timestamp:
Ken> 2014-10-23T00:00:00
You c
On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth
wrote:
> > "Ken" == Ken Tanzer writes:
>
> Ken> Hi. I've got a text field in a table that holds this style of
> Ken> timestamp:
>
> Ken> 2014-10-23T00:00:00
>
> You can't make this a field of type "timestamp" rather than text?
>
>
I actually c
> "Ken" == Ken Tanzer writes:
Ken> Hi. I've got a text field in a table that holds this style of
Ken> timestamp:
Ken> 2014-10-23T00:00:00
You can't make this a field of type "timestamp" rather than text?
Casts from text to either date or timestamp are mutable because they
depend on the
On Fri, Jan 4, 2019 at 2:27 PM Adrian Klaver
wrote:
> On 1/4/19 2:21 PM, Ken Tanzer wrote:
> >
> > I've tried various ways of getting to a date (::date, LEFT(x,10)::date,
> > etc.), but all of them throw the error "functions in index expression
> > must be marked IMMUTABLE."
>
> ?:
> '2014-10-23T
On 1/4/19 2:21 PM, Ken Tanzer wrote:
Hi. I've got a text field in a table that holds this style of timestamp:
2014-10-23T00:00:00
I'd like to be able to create an index on the date portion of this field
(as a date), because I have lots of queries that are searching for
particular dates or ra
Hi. I've got a text field in a table that holds this style of timestamp:
2014-10-23T00:00:00
I'd like to be able to create an index on the date portion of this field
(as a date), because I have lots of queries that are searching for
particular dates or ranges.
I've tried various ways of getting
On Fri, 4 Jan 2019, David G. Johnston wrote:
That would be the decision to make - does your toolkit support (or can be
made to support) the type and are you willing to choose a sub-optimal
database model because one or more applications happen to do things
differently?
IMO the daterange datatyp
On Fri, Jan 4, 2019 at 2:21 PM Rich Shepard wrote:
>Thinking more about duration perhaps I'm seeing a problem that really does
> not exist: it's a single column for both dates in the table while the UI
> needs separate date data entry widgets. Unless I use middleware code when a
> project row
On Fri, 4 Jan 2019, Rich Shepard wrote:
Thank you. That's a data type I've not before used.
Andreas,
Thinking more about duration perhaps I'm seeing a problem that really does
not exist: it's a single column for both dates in the table while the UI
needs separate date data entry widgets. U
On Fri, 4 Jan 2019, Andreas Kretschmer wrote:
Only if all projects have a known end_date; some don't.
that's not a problem:
test=*# create table projects(duration daterange default
daterange(current_date,null) check(lower(duration) is not null));
Andreas,
Thank you. That's a data type
I have a need for 32-bit binaries (pg_dump.exe, psql.exe, etc) that will work
with version 11.1 of the PostgreSQL server.
I'm not able to find these anywhere. Help, please?
Ken Benson
Am 04.01.19 um 18:32 schrieb Rich Shepard:
other solution for such 2 fields: you can use DATERANGE, only one field.
Only if all projects have a known end_date; some don't.
that's not a problem:
test=*# create table projects(duration daterange default
daterange(current_date,null) check(
On Fri, 4 Jan 2019, David G. Johnston wrote:
No. If no default is available and a value for the field is not provided
the stored value will be null; a default of null is thus also redundant
specification.
David,
Thanks for clarifying.
Regards,
Rich
On 1/4/19 10:26 AM, Rich Shepard wrote:
On Fri, 4 Jan 2019, Rob Sargent wrote:
Is the end_date always knowable at record insert?
Rob,
Not always. Sometimes projects have known end dates, other times the
end
is interminate until it happens.
CHECK(end_date is null or start_date <= end_
On Friday, January 4, 2019, Rich Shepard wrote:
> On Fri, 4 Jan 2019, David G. Johnston wrote:
>
> I wondered about this since NULL can be missing, unknown, or otherwise
>>> defined. Are there benefits to allowing an empty value in that column
>>> when
>>> checking that it's later than the star
On Fri, Jan 4, 2019 at 11:00 AM Pavel Stehule wrote:
> postgres=# create table test_table (col1 integer);
> CREATE TABLE
> postgres=# comment on table test_table is 'this is a table comment';
> COMMENT
> postgres=# \dt+
> List of relations
> ┌┬┬──
Hi
pá 4. 1. 2019 v 17:12 odesílatel Simon AUBERT
napsal:
> Hello,
>
> We can find this very informative blog post :
> https://blog.2ndquadrant.com/column-store-plans/
>
> And this wiki page :
> https://wiki.postgresql.org/wiki/ColumnOrientedSTorage
> I must say the approach with the "orientation
On Fri, 4 Jan 2019, David G. Johnston wrote:
I wondered about this since NULL can be missing, unknown, or otherwise
defined. Are there benefits to allowing an empty value in that column when
checking that it's later than the start date rather than explicitly setting
a default date after the st
On Friday, January 4, 2019, Rich Shepard wrote:
> On Fri, 4 Jan 2019, David G. Johnston wrote:
>
> The is null expression is redundant since check constraints pass when the
>> result is unknown.
>>
>
> David,
>
> I wondered about this since NULL can be missing, unknown, or otherwise
> defined.
On Fri, 4 Jan 2019, Andreas Kretschmer wrote:
no, you can use NULL, for instance. You don't need an explicit value.
But maybe you want to set the start_date to NOT NULL.
Andreas,
Yes, I added NOT NULL to the start_date column.
2. If so, please suggest a value for it.
other solution for
On Fri, 4 Jan 2019, David G. Johnston wrote:
The is null expression is redundant since check constraints pass when the
result is unknown.
David,
I wondered about this since NULL can be missing, unknown, or otherwise
defined. Are there benefits to allowing an empty value in that column when
On Fri, 4 Jan 2019, Rob Sargent wrote:
Is the end_date always knowable at record insert?
Rob,
Not always. Sometimes projects have known end dates, other times the end
is interminate until it happens.
CHECK(end_date is null or start_date <= end_date)
So a default of NULL should be appl
Hi,
VOPS, is one of the propotypes worked on
see
https://www.postgresql.org/message-id/4fb855c3-22b9-444f-21bf-114fa23cc...@postgrespro.ru
https://github.com/postgrespro/vops
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Friday, January 4, 2019, Rob Sargent wrote:
>
> CHECK(end_date is null or start_date <= end_date)
>
The is null expression is redundant since check constraints pass when the
result is unknown.
David J.
On Fri, 4 Jan 2019, Igor Korot wrote:
1. Do I need a DEFAULT value for the end_date?
2. If so, please suggest a value for it.
start_date.day() + 1?
Thanks, Igor. I did not pick up this syntax when I looked at data types
and their DDL usage.
Regards,
Rich
On 1/4/19 10:12 AM, Igor Korot wrote:
Hi, Rich,
On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard wrote:
I have a projects table that includes these two columns:
start_date date DEFAULT CURRENT_DATE,
end_date date
CONSTRAINT valid_start_date
CHECK (start_date <= end_date),
Am 04.01.19 um 17:53 schrieb Rich Shepard:
I have a projects table that includes these two columns:
start_date date DEFAULT CURRENT_DATE,
end_date date
CONSTRAINT valid_start_date
CHECK (start_date <= end_date),
1. Do I need a DEFAULT value for the end_date?
no, you can use NULL,
Hi, Rich,
On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard wrote:
>
>I have a projects table that includes these two columns:
>
> start_date date DEFAULT CURRENT_DATE,
> end_date date
> CONSTRAINT valid_start_date
> CHECK (start_date <= end_date),
>
>1. Do I need a DEFAULT val
Hi
pá 4. 1. 2019 v 17:57 odesílatel Mark Jeffcoat
napsal:
> I'm creating table and view comments with "COMMENT ON", and I can find
> the comment in pg_description, but I can't find a way to show the
> table comments using psql.
>
> $ psql --version
> psql (PostgreSQL) 11.1 (Debian 11.1-1+b2)
>
I'm creating table and view comments with "COMMENT ON", and I can find
the comment in pg_description, but I can't find a way to show the
table comments using psql.
$ psql --version
psql (PostgreSQL) 11.1 (Debian 11.1-1+b2)
I'd expect to see table comments by using \d+, and found an old post
on th
I have a projects table that includes these two columns:
start_date date DEFAULT CURRENT_DATE,
end_date date
CONSTRAINT valid_start_date
CHECK (start_date <= end_date),
1. Do I need a DEFAULT value for the end_date?
2. If so, please suggest a value for it.
TIA,
Rich
pá 4. 1. 2019 v 17:44 odesílatel Tom Lane napsal:
> Kristjan Tammekivi writes:
> > I've noticed a change in the behaviour in triggers / hstores in Postgres
> > 11.1 when compared to Postgres 10.5.
> > [ reference to OLD in an insert trigger doesn't throw error anymore ]
>
> Hmm. This seems to b
Kristjan Tammekivi writes:
> I've noticed a change in the behaviour in triggers / hstores in Postgres
> 11.1 when compared to Postgres 10.5.
> [ reference to OLD in an insert trigger doesn't throw error anymore ]
Hmm. This seems to be a side effect of the changes we (I) made in v11
to rationaliz
Hello,
We can find this very informative blog post :
https://blog.2ndquadrant.com/column-store-plans/
And this wiki page :
https://wiki.postgresql.org/wiki/ColumnOrientedSTorage
I must say the approach with the "orientation" option is a genius idea.
I won't discuss much the advantages of COS, I
On 1/4/19 4:48 AM, Achilleas Mantzios wrote:
On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
Some months ago I wrote a little application with Python+Django which
stores
blob data in bytearrays.
It works.
In the future there will be a lot more traffic, and I am unsure
if this is really a good so
I did say you need to run with different binaries for different versions:
To put it simply: you cannot run different major versions of PostgreSQL
with the same binaries.
So when I subsequently said the following it was in that context.
The 3rd one is separate binary locations for each PG clu
> On Jan 4, 2019, at 9:32 AM, MichaelDBA wrote:
>
> The 3rd one is separate binary locations for each PG cluster instance
> running on the same host.
Don’t need separate binaries for each cluster; only separate binaries for each
version needed; i.e. 9.6.1, 9.6.2, etc.
On 1/4/19 7:30 AM, Erika Knihti-Van Driessche wrote:
Hi,
Thanks all for your replies and help! I already thought that it’ll be
possible to just install binaries in different locations. My current
installation I have all done using one binary location and initdb.. not
a very good solution on h
Hi,
Thanks all for your replies and help! I already thought that it’ll be possible
to just install binaries in different locations. My current installation I have
all done using one binary location and initdb.. not a very good solution on
hindsight.. Oh, and I’m on RHEL 7. I used the installati
From: chiru r
> I have tried to intall the RPMs with -relocate option,however it is not
> working as expected and throwing below error.
>
> [root@Server1dev:/root/PG11]#
> #-> rpm -ivh --relocate /usr/pgsql-11/=/u01/postgres/pg11_relocate/
> postgresql11-server-11.1-1PGDG.rhel7.x86_64.rpm
> war
Hi Erika,
You can create the different version services and use these services for
PostgreSQL start/stop.
ex.
service postgresql-10 start
service postgresql-9.6 start
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Fri, Jan 4, 2019 at 7:51 PM Erika Knihti-Van Driessche <
erika.kni
On 04/01/2019 14:21, Erika Knihti-Van Driessche wrote:
Hi,
I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same binaries
though, and this is now giving me some headache.. I cannot shutdown all
clusters at the same t
On 1/4/19 6:21 AM, Erika Knihti-Van Driessche wrote:
Hi,
I have 10+ postgresql clusters (v 9.6) running on one machine - each
having their own data directory and port. They all share same binaries
though, and this is now giving me some headache.. I cannot shutdown all
clusters at the same tim
On 1/4/19 4:20 AM, Kristjan Tammekivi wrote:
Hi,
I've read the documentation, that's why I said this might be
undocumented. Try the SQL in Postgres 11 and see that it works for yourself.
I have an analogous trigger in production from yesterday and I've tested
it in local environment as well.
To put it simply: you cannot run different major versions of PostgreSQL
with the same binaries. 3 things need to be separate. You named 2 of
them: data directory and port. The 3rd one is separate binary locations
for each PG cluster instance running on the same host.
What I do is create a se
Hi,
I have 10+ postgresql clusters (v 9.6) running on one machine - each having
their own data directory and port. They all share same binaries though, and
this is now giving me some headache.. I cannot shutdown all clusters at the
same time, so upgrading them is quite impossible.
I know that run
Am 04.01.19 um 12:48 schrieb Achilleas Mantzios:
On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.
It works.
In the future there will be a lot more traffic, and I am unsure
if this is really a goo
Hi,
I've read the documentation, that's why I said this might be undocumented.
Try the SQL in Postgres 11 and see that it works for yourself.
I have an analogous trigger in production from yesterday and I've tested it
in local environment as well.
On Fri, Jan 4, 2019 at 12:56 PM Charles Clavadetsc
On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.
It works.
In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG
Hi All,
Hi all, I need some help over barman..Recently, I refreshed one of non-prod
environments with production database and I ran into this problem or rather
unforeseen point that I cannot change the system catalogs ownership to the
respective non prod owner account. And I really don't want t
Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.
It works.
In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG issue.
What do you think?
Which alternativ
Thank you!
Something like
```
with t as (
select set_config('ns.blah', '1', false) as res
)
select res from t;
select current_setting('ns.blah');
```
works for me.
Andrew Gierth 于2019年1月4日周五 下午6:27写道：
>
> > "Zexuan" == Zexuan Luo writes:
>
> Zexuan> For instance:
> Zexuan> ```
>
Hello
From: Kristjan Tammekivi
Sent: Freitag, 4. Januar 2019 11:46
To: pgsql-gene...@postgresql.org
Subject: Potentially undocumented behaviour change in Postgres 11 concerning
OLD record in an after insert trigger
Hi,
I've noticed a change in the behaviour in triggers / hstores in P
Hi,
I've noticed a change in the behaviour in triggers / hstores in Postgres
11.1 when compared to Postgres 10.5.
The following won't work on Postgres 10.5 but in Postgres 11.1 it works
just fine:
CREATE EXTENSION hstore;
CREATE TABLE _tmp_test1 (id serial PRIMARY KEY, val INTEGER);
CREATE TABLE
> "Zexuan" == Zexuan Luo writes:
Zexuan> For instance:
Zexuan> ```
Zexuan> with t as (
Zexuan> select set_config('blah', '1', false)
Zexuan> )
Zexuan> select current_setting('blah');
A CTE containing a SELECT query which is not referenced anywhere will
not be executed, even if
On Fri, Jan 4, 2019 at 3:37 AM Zexuan Luo wrote:
> For instance:
> ```
> with t as (
> select set_config('blah', '1', false)
> )
> select current_setting('blah');
> select current_setting('blah');
> ```
>
> Execute queries above gets these error messages:
> psql:test-query-dump.sql:4: ERR
For instance:
```
with t as (
select set_config('blah', '1', false)
)
select current_setting('blah');
select current_setting('blah');
```
Execute queries above gets these error messages:
psql:test-query-dump.sql:4: ERROR: unrecognized configuration parameter "blah"
psql:test-query-dump.sq
65 matches
Mail list logo
