I understand and agree.
Anyway, we suggest our customers to use sequences instead of serials.
Seb
From: Alban Hertroys
Sent: Wednesday, March 29, 2023 10:15 PM
To: Sebastien Flaesch
Cc: Adrian Klaver ; Kirk Wolak ;
Geoff Winkless ; pgsql-general
Subject: Re:
> You could for example (when looking for a MAX) skip scanning
block ranges whose indexed MAX is less than the indexed MIN of some
other block range.
When looking for a max, you could scan the range with the maximal indexed
MAX, and then you could skip all the ranges that have MAX less than the
al
On Thu, 30 Mar 2023 at 17:18, Tom Lane wrote:
>
> Julien Rouhaud writes:
> > brin indexes don't work the way you would hope for. the stored min/max
> > values per range guarantees that all values in the underlying relation
> > pages are contained in that range, but it doesn't mean that those min
> so you can't deduce in which range the current min or max value is from
there.
That is why you select several candidate ranges and scan the table for
those ranges.
For instance, if you have ranges
1) 1..4
2) 5..8
3) 6..9
Then you do something like
select x
from (
select max(col) x from
Julien Rouhaud writes:
> brin indexes don't work the way you would hope for. the stored min/max
> values per range guarantees that all values in the underlying relation
> pages are contained in that range, but it doesn't mean that those min/max
> values are still present in the table, so you can'
On Thu, 30 Mar 2023, 05:03 Andrey Klochkov, wrote:
> BRIN indexes seem to work perfectly well for our purposes, and they are so
> tiny compared to B-Tree. Selecting min/max values is very expensive though.
>
> In my case the table is ~2.5TB (530M records), while the whole BRIN index
> is 16MB. I
Right you are I should have divided it by ranges per page.
Vladimir
--
Vladimir
On Wed, Mar 29, 2023 at 7:39 PM Ron wrote:
> Something like this?
>
> ALTER TABLE foo
> ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE
> USING to_timestamp(update_ts, 'MMDDHH24miSSMS');
>
> That would definitely minimize the possibility of errors.
>
Yes.
David J.
On 3/29/23 21:06, David G. Johnston wrote:
On Wed, Mar 29, 2023 at 6:51 PM Ron wrote:
It would be really helpful to be able to reposition columns in
tables. That
way, one could:
add the new TIMESTAMP column,
populate it using to_timestamp(),
drop the text column,
re
On Wed, Mar 29, 2023 at 6:51 PM Ron wrote:
> It would be really helpful to be able to reposition columns in tables.
> That
> way, one could:
> add the new TIMESTAMP column,
> populate it using to_timestamp(),
> drop the text column,
> reposition the TIMESTAMP column to where it "should" be.
>
>
I
On 3/29/23 18:31, Adrian Klaver wrote:
On 3/29/23 16:24, Ron wrote:
Postgresql 13.10
$ psql -h myhost.example.com -X dba \
-c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH
DELIMITER '|';"
ERROR: date/time field value out of range: "2013061914122501"
CONTEXT: COPY t_id_mas
On 3/29/23 19:20, Thorsten Glaser wrote:
On Wed, 29 Mar 2023, Ron wrote:
There are 550+ tables, so something that I can do once on this end would make
my life a lot easier.
Some quick perl or awk or shell job to batch-change the field
to an accepted syntax is probably quicker.
Even easier wo
On Wed, 29 Mar 2023, Ron wrote:
> There are 550+ tables, so something that I can do once on this end would make
> my life a lot easier.
Some quick perl or awk or shell job to batch-change the field
to an accepted syntax is probably quicker.
bye,
//mirabilos
--
15:41⎜ Somebody write a testsuite
On 3/29/23 16:31, Adrian Klaver wrote:
On 3/29/23 16:24, Ron wrote:
Postgresql 13.10
$ psql -h myhost.example.com -X dba \
-c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH
DELIMITER '|';"
ERROR: date/time field value out of range: "2013061914122501"
CONTEXT: COPY t_id_mas
On 3/29/23 16:24, Ron wrote:
Postgresql 13.10
$ psql -h myhost.example.com -X dba \
-c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH
DELIMITER '|';"
ERROR: date/time field value out of range: "2013061914122501"
CONTEXT: COPY t_id_master, line 1, column update_timestamp:
"
Postgresql 13.10
$ psql -h myhost.example.com -X dba \
-c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH
DELIMITER '|';"
ERROR: date/time field value out of range: "2013061914122501"
CONTEXT: COPY t_id_master, line 1, column update_timestamp: "2013061914122501"
The timestamp
BRIN indexes seem to work perfectly well for our purposes, and they are so
tiny compared to B-Tree. Selecting min/max values is very expensive though.
In my case the table is ~2.5TB (530M records), while the whole BRIN index
is 16MB. I think it'd be totally fine to scan all BRIN pages, it'd be way
On Wed, 29 Mar 2023 at 22:07, Vladimir Sitnikov
wrote:
> > Is it correct that BRIN indexes don't support MIN/MAX operations ?
> In theory, it should be possible to implement min/max scan support for BRIN,
> however it is not implemented yet.
>
> Just in case, min/max query would require to read a
On Wed, 29 Mar 2023 at 21:11, Sebastien Flaesch
wrote:
> Oh the use of default keyword is new to me, thanks for that.
> But to make PostgreSQL more Informix-compatible, zero should have been
> considered as well.
NONONO please! Someone already pointed a sequence can generate zero,
but even witho
> On 29 Mar 2023, at 21:11, Sebastien Flaesch wrote:
>
> Oh the use of default keyword is new to me, thanks for that.
>
> But to make PostgreSQL more Informix-compatible, zero should have been
> considered as well.
…No, I’m not going to be humble about this opinion… Postgres does a sane thin
> Is it correct that BRIN indexes don't support MIN/MAX operations ?
In theory, it should be possible to implement min/max scan support for
BRIN, however it is not implemented yet.
Just in case, min/max query would require to read all BRIN pages, and then
it would require to read the correspondin
On 3/29/23 12:11, Sebastien Flaesch wrote:
Oh the use of default keyword is new to me, thanks for that.
But to make PostgreSQL more Informix-compatible, zero should have been
considered as well.
1) Why? Down the road to compatibility with some undetermined group of
databases lies mayhem.
> On Mar 29, 2023, at 12:11, Sebastien Flaesch
> wrote:
> But to make PostgreSQL more Informix-compatible, zero should have been
> considered as well.
There is an infinite family of strange features that various databases have
(DUAL from Oracle, anyone?); PostgreSQL will rapidly become unus
On 3/29/23 09:43, Peter J. Holzer wrote:
On 2023-03-29 07:59:54 -0700, Adrian Klaver wrote:
On 3/29/23 07:19, Sebastien Flaesch wrote:
INSERT statements must not use the serial column, so you have to list
all columns of the table and provide only the values of the non-serial
columns. With Infor
Oh the use of default keyword is new to me, thanks for that.
But to make PostgreSQL more Informix-compatible, zero should have been
considered as well.
Informix:
sf@toro:/tmp$ dbaccess test1 -
Database selected.
> create table mytable ( pkey serial not null primary key, name varchar(50) );
Tabl
Ok... sounds not good all in all.
Appreciate your help!
Thanks!
From: Laurenz Albe
Sent: Wednesday, March 29, 2023 5:53 PM
To: Sebastien Flaesch ; Kirk Wolak
Cc: Geoff Winkless ; pgsql-general
Subject: Re: Using CTID system column as a "temporary" primary key
E
Hello,
We've started experimenting with using BRIN indexes for some of our large
tables, as a replacement for B-Tree on "natural" timestamp columns that
seem to be a good case for BRIN. Is it correct that BRIN indexes don't
support MIN/MAX operations ?
We see that a query like `SELECT max(timestam
On 2023-03-29 07:59:54 -0700, Adrian Klaver wrote:
> On 3/29/23 07:19, Sebastien Flaesch wrote:
> > INSERT statements must not use the serial column, so you have to list
> > all columns of the table and provide only the values of the non-serial
> > columns. With Informix you could just specific a z
On Wed, 2023-03-29 at 14:23 +, Sebastien Flaesch wrote:
> From: Laurenz Albe
> > It is safe to assume that the CTID is stable within a single transaction
> > only if you use REPEATABLE READ or better transaction isolation level.
> >
> > With READ COMMITTED, you see updated rows (and consequent
On 3/29/23 07:19, Sebastien Flaesch wrote:
Hello Kirk,
INSERT statements must not use the serial column, so you have to list
all columns of the table and provide only the values of the non-serial
columns. With Informix you could just specific a zero to get a new
generated serial, but seems
Laurent,
Thanks for the advice about REPEATABLE READ isolation level!
Seb
From: Laurenz Albe
Sent: Wednesday, March 29, 2023 1:08 PM
To: Kirk Wolak ; Sebastien Flaesch
Cc: Geoff Winkless ; pgsql-general
Subject: Re: Using CTID system column as a "temporary" pr
Hello Kirk,
We are pushing our customers to use only "pure" SQL without writing triggers or
stored procedures, to not be stuck with a specific DB vendor.
We have a quite good vision of what is SQL portable and what is not SQL
portable.
Concurrent data access is one these topic, especially when
On Wed, Mar 29, 2023 at 3:45 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. Selecting/fetching an empty 1D array using a binary-mode PGresult,
> > gives me back 12 bytes, and the first 4, the rank, is 0, something I was
> > not expecting.
>
> Yeah, empty arrays have zero dimensions.
>
>
Dominique Devienne writes:
> Hi. Selecting/fetching an empty 1D array using a binary-mode PGresult,
> gives me back 12 bytes, and the first 4, the rank, is 0, something I was
> not expecting.
Yeah, empty arrays have zero dimensions.
> PS: On a side note; where can I find the sending code for arr
Hi. Selecting/fetching an empty 1D array using a binary-mode PGresult,
gives me back 12 bytes, and the first 4, the rank, is 0, something I was
not expecting.
I was expecting dims_rank = 1, then first_dim = 0
Normal? Next two ints are kinda useless given the 0 rank?
It's easy to fix, to support ra
basti schrieb am 29.03.2023 um 14:57:
> https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Util/Aggregation.php
>
> There are several problems.
>
> One of that ist the 'REPLACE INTO'.
Most probably INSERT ... ON CONFLICT
> An other problem is '@prev_timestamp := timestamp'
That's t
Hello, I try to move from MySQL to PostgreSQL.
All seems to work expect of one query.
The query is part of https://github.com/volkszaehler/volkszaehler.org
https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Util/Aggregation.php
There are several problems.
One of that ist the 'REP
On Tue, 2023-03-28 at 14:24 -0400, Kirk Wolak wrote:
>
> I cringe at the thought of using CTID. And while it's probably "safe enough"
> inside a single transaction. I doubt that there is much "testing" of this
> concept.
It is safe to assume that the CTID is stable within a single transaction
38 matches
Mail list logo
