Re: [GENERAL] pg_xlog size growing untill it fills the partition
On Oct 3, 2013, at 23:56, Michal TOMA wrote: > I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2.13). > My pg_xlog directory is growing uncontrolably untill it fills the partition. > The database is under heavy write load and is spread on two tablesapces one > on a ssd software raid1 partition and a second one on a hdd software raid1 > partition. > I have no wal archiving enabled nor any replication. > > I have tried different checkpoint related parameters without any noticable > improvement. > Now I have: > checkpoint_completion_target = 0.9 > wal_buffers = 8MB > checkpoint_segments = 16 > checkpoint_timeout = 20min > shared_buffers = 2GB > log_checkpoints = on > > This is what I can see in the log: > 2013-10-03 13:58:56 CEST LOG: checkpoint starting: xlog > 2013-10-03 13:59:56 CEST LOG: checkpoint complete: wrote 448 buffers > (0.2%); 0 transaction log file(s) added, 9 removed, 18 recycled; write=39.144 > s, sync=21.136 s, total=60.286 s; sync files=380, longest=14.517 s, > average=0.055 s > 2013-10-03 14:04:07 CEST LOG: checkpoint starting: xlog > 2013-10-03 15:27:01 CEST LOG: checkpoint complete: wrote 693 buffers > (0.3%); 0 transaction log file(s) added, 0 removed, 16 recycled; write=90.775 > s, sync=4883.295 s, total=4974.074 s; sync files=531, longest=152.855 s, > average=9.196 s > 2013-10-03 15:27:01 CEST LOG: checkpoint starting: xlog time > 2013-10-03 19:06:30 CEST LOG: checkpoint complete: wrote 3467 buffers > (1.3%); 0 transaction log file(s) added, 0 removed, 16 recycled; > write=122.555 s, sync=13046.077 s, total=13168.637 s; sync files=650, > longest=234.697 s, average=20.069 s > 2013-10-03 19:06:30 CEST LOG: checkpoint starting: xlog time > 2013-10-03 22:30:25 CEST LOG: checkpoint complete: wrote 10198 buffers > (3.9%); 0 transaction log file(s) added, 216 removed, 33 recycled; > write=132.229 s, sync=12102.311 s, total=12234.608 s; sync files=667, > longest=181.374 s, average=18.144 s > 2013-10-03 22:30:25 CEST LOG: checkpoint starting: xlog time I'm not too familiar with checkpoint logging output, but from the looks of it you're literally spending hours on syncing checkpoints. Are those disks on a RAID controller with a failed cache battery or something? You aren't using RAID-5, are you? > - > > When the server is up and running under the usual load I get the following > results: > - > 2 seconds per test > O_DIRECT supported on this platform for open_datasync and open_sync. > > Compare file sync methods using one 8kB write: > (in wal_sync_method preference order, except fdatasync > is Linux's default) >open_datasync 0.369 ops/sec >fdatasync 0.575 ops/sec >fsync 0.125 ops/sec >fsync_writethroughn/a >open_sync 0.222 ops/sec > > Compare file sync methods using two 8kB writes: > (in wal_sync_method preference order, except fdatasync > is Linux's default) >open_datasync 0.383 ops/sec >fdatasync 2.171 ops/sec >fsync 1.318 ops/sec >fsync_writethroughn/a >open_sync 0.929 ops/sec > > Compare open_sync with different write sizes: > (This is designed to compare the cost of writing 16kB > in different write open_sync sizes.) > 1 * 16kB open_sync write 0.079 ops/sec > 2 * 8kB open_sync writes 0.041 ops/sec > 4 * 4kB open_sync writes 0.194 ops/sec > 8 * 2kB open_sync writes 0.013 ops/sec >16 * 1kB open_sync writes 0.005 ops/sec > > Test if fsync on non-write file descriptor is honored: > (If the times are similar, fsync() can sync data written > on a different descriptor.) >write, fsync, close 0.098 ops/sec >write, close, fsync 0.067 ops/sec > > Non-Sync'ed 8kB writes: >write 0.102 ops/sec > - Those numbers look bad. Are these the SSD's or the software RAID? It's almost as if you're saturating your disk I/O bandwidth. What hardware is involved here? Or is it a kernel limitation, perhaps? > I need to tell to the server to limit the amount of wal files in pg_xlog > somehow whatever the efect on the performance could be. I think more's at play here. Unfortunately, if it's not directly related to the things I mentioned I can't help much. I'm a bit out of my lea
Re: [GENERAL] Large objects system
Rafael B.C. wrote: > I am dealing with the old decision about hiw to store data objects and trying > to understand deep the > postgre system including toast, pg-largedataobject table and so on. > > My real doubt right now is why bytea does not gets processed by toast system > even when is grow enough. > Since ive read that tuples are not allowed to expand over several dtabase > pages. > > Maybe someone has the explanaition for this behavior? What makes you think that bytea won't get TOASTed? It sure should. Yours, Laurenz Albe -- 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] Large objects system
On 10/3/2013 2:22 AM, Rafael B.C. wrote: My real doubt right now is why bytea does not gets processed by toast system even when is grow enough. Since ive read that tuples are not allowed to expand over several dtabase pages. a tuple can't expand over ONE database page, and generally it prefers packing several to many tuples per page. any bytea field over a few bytes should be getting toasted. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] pgbench
A 2013-10-03 17:50, Alvaro Herrera escrigué: Giuseppe Broccolo wrote: The format of the script file has to be one SQL command per line; multiline SQL commands are not supported, and empty lines are ignored. This could bring to errors. Could this be your case? Multiline SQL commands are not supported? Well that sucks, because only BUFSIZ chars are read from each line. In my platform that's 8192, but maybe in Simeó's case it's shorter .. or maybe his query really is longer than 8192 bytes. This smells like a pgbench bug to me. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services Álvaro, you hit the nail on the head! I have seen that in stdio.h BUFSIZ is defined like 1024 in freeBSD 9.0, probably for this reason I can't test this query (1657 characters), but I'm able to test shorter queries. Finally I have done a plsql procedure to call the query from pgbench. Meanwhile I will try to figure out if I can increase this variable without affect the system. Thanks to all Simeó Reig Barcelona (Spain) -- 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] pg_xlog size growing untill it fills the partition
Hello Alban, Yes I think I'm saturating the HDD IO but the problem is that the only thing that runs on that server is postgres itself. I have two tablespeces one on a SSD software raid 1 another one on a HDD software raid 1. The disk which is saturating is the HDD and the pg_xlog directory is on the SSD. So the problem seems to be that the HDD is saturating while the SSD is not. The server is able to write wal files on the SSD really fast while writing data from the wal files to the HDD is much slower. The question is how do i solve this issue. It must be a way to tell postgres to regulate the application making the INSERT requests even if there is no IO problem on the pg_xlog/SSD partition (but at the same time there is a huge one on the data/HDD partition). Something like "if total pg_xlog size is more than 20 GB" let all INSERT queries wait till the data is commited on the HDD. For the only solution I see is to manually restart the server every time the size of pg_xlog is close to filling the partition. This is definitely not an option. Michal On Friday 04 October 2013 09:01:53 Alban Hertroys wrote: > On Oct 3, 2013, at 23:56, Michal TOMA wrote: > > I have a problem on my pg 9.2.4 setup (OpenSuse 12.2, kernel 3.2.13). > > My pg_xlog directory is growing uncontrolably untill it fills the > > partition. The database is under heavy write load and is spread on two > > tablesapces one on a ssd software raid1 partition and a second one on a > > hdd software raid1 partition. I have no wal archiving enabled nor any > > replication. > > > > I have tried different checkpoint related parameters without any > > noticable improvement. Now I have: > > checkpoint_completion_target = 0.9 > > wal_buffers = 8MB > > checkpoint_segments = 16 > > checkpoint_timeout = 20min > > shared_buffers = 2GB > > log_checkpoints = on > > > > This is what I can see in the log: > > 2013-10-03 13:58:56 CEST LOG: checkpoint starting: xlog > > 2013-10-03 13:59:56 CEST LOG: checkpoint complete: wrote 448 buffers > > (0.2%); 0 transaction log file(s) added, 9 removed, 18 recycled; > > write=39.144 s, sync=21.136 s, total=60.286 s; sync files=380, > > longest=14.517 s, average=0.055 s > > > > 2013-10-03 14:04:07 CEST LOG: checkpoint starting: xlog > > 2013-10-03 15:27:01 CEST LOG: checkpoint complete: wrote 693 buffers > > (0.3%); 0 transaction log file(s) added, 0 removed, 16 recycled; > > write=90.775 s, sync=4883.295 s, total=4974.074 s; sync files=531, > > longest=152.855 s, average=9.196 s 2013-10-03 15:27:01 CEST LOG: > > checkpoint starting: xlog time > > 2013-10-03 19:06:30 CEST LOG: checkpoint complete: wrote 3467 buffers > > (1.3%); 0 transaction log file(s) added, 0 removed, 16 recycled; > > write=122.555 s, sync=13046.077 s, total=13168.637 s; sync files=650, > > longest=234.697 s, average=20.069 s 2013-10-03 19:06:30 CEST LOG: > > checkpoint starting: xlog time > > 2013-10-03 22:30:25 CEST LOG: checkpoint complete: wrote 10198 buffers > > (3.9%); 0 transaction log file(s) added, 216 removed, 33 recycled; > > write=132.229 s, sync=12102.311 s, total=12234.608 s; sync files=667, > > longest=181.374 s, average=18.144 s 2013-10-03 22:30:25 CEST LOG: > > checkpoint starting: xlog time > > I'm not too familiar with checkpoint logging output, but from the looks of > it you're literally spending hours on syncing checkpoints. > > Are those disks on a RAID controller with a failed cache battery or > something? You aren't using RAID-5, are you? > > > - > > > > > > When the server is up and running under the usual load I get the > > following results: > > - > > 2 seconds per test > > O_DIRECT supported on this platform for open_datasync and open_sync. > > > > Compare file sync methods using one 8kB write: > > (in wal_sync_method preference order, except fdatasync > > is Linux's default) > >open_datasync 0.369 ops/sec > >fdatasync 0.575 ops/sec > >fsync 0.125 ops/sec > >fsync_writethroughn/a > >open_sync 0.222 ops/sec > > > > Compare file sync methods using two 8kB writes: > > (in wal_sync_method preference order, except fdatasync > > is Linux's default) > >open_datasync 0.383 ops/sec > >fdatasync 2.171 ops/sec > >fsync 1.318 ops/sec > >fsync_writethroughn/a > >open_sync 0.929 ops/sec > > > > Compare open_sync with different write sizes: > > (This is designed to compare the cost of writing 16kB > > in different write open_sync s
[GENERAL] Re: [GENERAL] Re: [GENERAL] Help on ṕerformance
Hey short trick :
to avoid to use the schema name multiple time (more readable and more easy
to re use).
You can use the
SET search_path gpstracking_device_tracks, public;
(see manual here :
http://www.postgresql.org/docs/current/static/sql-set.html)
Cheers,
Rémi-C
2013/10/2 Carlos Eduardo Sotelo Pinto
> Thanks to all
>
> I have fix that refactoring the function
>
> BEGIN
> arr := regexp_split_to_array(_imeis, E'\\s+');
> RETURN QUERY
> SELECT
> gpstracking_device_tracks.imei,
> gpstracking_device_tracks.device_id,
> gpstracking_device_tracks.date_time_process,
> gpstracking_device_tracks.latitude,
> gpstracking_device_tracks.longitude,
> gpstracking_device_tracks.course,
> gpstracking_device_tracks.speed,
> gpstracking_device_tracks.mileage,
> gpstracking_device_tracks.gps_signal,
> gpstracking_device_tracks.gsm_signal,
> gpstracking_device_tracks.alarm_status,
> gpstracking_device_tracks.gps_status,
> gpstracking_device_tracks.vehicle_status,
> gpstracking_device_tracks.alarm_over_speed,
> gpstracking_device_tracks.other,
> gpstracking_device_tracks.address
> FROM (
> SELECT
> gpstracking_device_tracks.imei,
> gpstracking_device_tracks.device_id,
> gpstracking_device_tracks.date_time_process,
> gpstracking_device_tracks.latitude,
> gpstracking_device_tracks.longitude,
> gpstracking_device_tracks.course,
> gpstracking_device_tracks.speed,
> gpstracking_device_tracks.mileage,
> gpstracking_device_tracks.gps_signal,
> gpstracking_device_tracks.gsm_signal,
> gpstracking_device_tracks.alarm_status,
> gpstracking_device_tracks.gps_status,
> gpstracking_device_tracks.vehicle_status,
> gpstracking_device_tracks.alarm_over_speed,
> gpstracking_device_tracks.other,
> gpstracking_device_tracks.address,
> ROW_NUMBER() OVER(PARTITION BY gpstracking_device_tracks.imei ORDER BY
> gpstracking_device_tracks.date_time_process DESC) as rnumber
> FROM gpstracking_device_tracks
> WHERE gpstracking_device_tracks.imei = ANY(arr)
> AND gpstracking_device_tracks.date_time_process >= date_trunc('hour',
> now())
> AND gpstracking_device_tracks.date_time_process <= NOW()
> ) AS gpstracking_device_tracks
> WHERE gpstracking_device_tracks.rnumber = 1;
> END;
>
>
> 2013/10/2 Merlin Moncure
>
>> On Mon, Sep 30, 2013 at 10:03 AM, Carlos Eduardo Sotelo Pinto
>> wrote:
>> >
>> > I need a help on postgresql performance
>> >
>> > I have configurate my postgresql files for tunning my server, however
>> it is
>> > slow and cpu resources are highter than 120%
>> >
>> > I have no idea on how to solve this issue, I was trying to search more
>> infor
>> > on google but is not enough, I also have try autovacum sentences and
>> reindex
>> > db, but it continues beeing slow
>> >
>> > My app is a gps listener that insert more than 6000 records per minutes
>> > using a tcp server developed on python twisted, where there is no
>> problems,
>> > the problem is when I try to follow the gps devices on a map on a
>> relatime,
>> > I am doing queries each 6 seconds to my database from my django app, for
>> > request last position using a stored procedure, but the query get slow
>> on
>> > more than 50 devices and cpu start to using more than 120% of its
>> resources
>> >
>> > Django App connect the postgres database directly, and tcp listener
>> server
>> > for teh devices connect database on threaded way using pgbouncer, I
>> have not
>> > using my django web app on pgbouncer caause I dont want to crash gps
>> devices
>> > connection on the pgbouncer
>> >
>> > I hoe you could help on get a better performance
>> >
>> > I am attaching my store procedure, my conf files and my cpu, memory
>> > information
>> >
>> > **Stored procedure**
>> >
>> > CREATE OR REPLACE FUNCTION gps_get_live_location (
>> > _imeis varchar(8)
>> > )
>> > RETURNS TABLE (
>> > imei varchar,
>> > device_id integer,
>> > date_time_process timestamp with time zone,
>> > latitude double precision,
>> > longitude double precision,
>> > course smallint,
>> > speed smallint,
>> > mileage integer,
>> > gps_signal smallint,
>> > gsm_signal smallint,
>> > alarm_status boolean,
>> > gsm_status boolean,
>> > vehicle_status boolean,
>> > alarm_over_speed boolean,
>> > other text,
>> > address varchar
>> > ) AS $func$
>> > DECLARE
>> > arr varchar[];
>> > BEGIN
>> > arr := regexp_split_to_array(_imeis, E'\\s+');
>> > FOR i IN 1..array_length(arr, 1) LOOP
>> > RETURN QUERY
>> > SELECT
>> > gpstracking_device_tracks.imei,
>> > gpstracking_device_tracks.device_id,
>> > gpstracking_device_tracks.date_time_process,
>> > gpstracking_device_tracks.latitude,
>> > gpstracking_device_tracks.longitude,
>> > gpstracking_device_tracks.course,
>> > gpstracking_device_tracks.speed,
>> > gpstracking_device_tracks.mileage,
>> > gpstracking_device_tracks.gps_signal,
>> > gpstracking_device_tracks.gsm_signal,
>> > gpstracking_device_t
[GENERAL] [Q] Table aliasing
Hello. I have a noob question about table aliases in SQL. Suppose the following query: SELECT * FROM deal WHERE deal.id IN ( SELECT DISTINCT deal.id FROM deal JOIN partner_share ON deal.id = partner_share.deal_id JOIN partner ONshare.partner_id = partner.id WHERE partner.team_id = 12345 AND ( partner_share.type = 1 AND deal.external_id IS NOT NULL OR partner_share.type = 2 AND deal.external_id IS NULL ) ) As you can see, the IN (...) references the deal table too. My questions: * Should I alias one of the references to deal? * The above query works the same both with and without an alias, so is it simply a matter of taste / good practice? * Where can I find more info about this, i.e. when the alias is mandatory and when it is only a convenience? I've scanned through http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html but it only mentions that subselect in FROM must have an alias. I would like to know about IN (...) and EXISTS (...). Thank you, L. -- 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] Table aliasing
I almost always alias my tables by default with something short (Usually 1 - 3 characters), but not my subselects for an in list. In this case I would do d1, d2, ps, and p for the different tables. I then do my best to use the same alias in all my queries. I am also big on formatting the SQL here is how I would write what you have for readability SELECT * FROM deal AS d1 WHERE d1.deal.id IN ( SELECT DISTINCT deal.id FROM deal AS d2 INNER JOIN partner_share AS ps ON d2.deal.id = ps.deal_id INNER JOIN partner AS p ON ps.partner_id = p.partner.id WHERE p.team_id = 12345 AND (ps.type = 1 AND d2.external_id IS NOT NULL OR ps.type = 2 AND d2.external_id IS NULL) ) On Fri, Oct 4, 2013 at 5:59 AM, Ladislav Lenart wrote: > Hello. > > I have a noob question about table aliases in SQL. > > Suppose the following query: > > SELECT * > FROM deal > WHERE > deal.id IN ( > SELECT DISTINCT deal.id > FROM > deal > JOIN partner_share ON deal.id = partner_share.deal_id > JOIN partner ONshare.partner_id = partner.id > WHERE > partner.team_id = 12345 > AND ( > partner_share.type = 1 AND deal.external_id IS NOT NULL > OR partner_share.type = 2 AND deal.external_id IS NULL > ) > ) > > As you can see, the IN (...) references the deal table too. > > My questions: > * Should I alias one of the references to deal? > * The above query works the same both with and without an alias, so is it > simply > a matter of taste / good practice? > * Where can I find more info about this, i.e. when the alias is mandatory > and > when it is only a convenience? I've scanned through > > > http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html > > but it only mentions that subselect in FROM must have an alias. I would > like to > know about IN (...) and EXISTS (...). > > > Thank you, > > L. > > > > -- > 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] Table aliasing
Ladislav Lenart wrote > * Should I alias one of the references to deal? > * The above query works the same both with and without an alias, so is it > simply > a matter of taste / good practice? > * Where can I find more info about this, i.e. when the alias is mandatory > and > when it is only a convenience? I've scanned through > > > http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html > > but it only mentions that subselect in FROM must have an alias. I would > like to > know about IN (...) and EXISTS (...). Aliases are mandatory for self-joins. Basically two relations at the same query level cannot have the same name. With sub-selects the inner level shields the outer level from being visible so an alias is optional. I believe the case of a correlated sub-query requires the alias for the same reason - otherwise you end up with a always true where clause when joining the supposed two deal tables together. There is no right/best way but it is always more clear to rename such that you avoid repeating the same relation name in the query. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Q-Table-aliasing-tp5773355p5773364.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] [Q] Table aliasing
On 4.10.2013 15:11, David Johnston wrote: > Ladislav Lenart wrote >> * Should I alias one of the references to deal? >> * The above query works the same both with and without an alias, so is it >> simply >> a matter of taste / good practice? >> * Where can I find more info about this, i.e. when the alias is mandatory >> and >> when it is only a convenience? I've scanned through >> >> >> http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html >> >> but it only mentions that subselect in FROM must have an alias. I would >> like to >> know about IN (...) and EXISTS (...). > > Aliases are mandatory for self-joins. Basically two relations at the same > query level cannot have the same name. With sub-selects the inner level > shields the outer level from being visible so an alias is optional. I > believe the case of a correlated sub-query requires the alias for the same > reason - otherwise you end up with a always true where clause when joining > the supposed two deal tables together. > > There is no right/best way but it is always more clear to rename such that > you avoid repeating the same relation name in the query. I think I understand. Thank you, 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
[GENERAL] Reasons to reorder results *within* a transaction?
I *know* that without an ORDER BY clause, the database is free to reorder results in any way it likes. However, I recently ran into a case where the *SAME* query was returning results in a different order *within* a single transaction, which surprised me (we fixed it by adding the missing ORDER BY). I would assume that once a transaction obtains a snapshot, all its read operations would return the same results. Could concurrent updates in other transactions "move" tuples in the underlying heap files? Could the query optimizer decide to execute a query two different ways for some reason (e.g. statistics collected after the first query?). Clearly the way Postgres works internally is a bit different from what I assumed. Any references to docs I should read would be appreciated. Roughly speaking, the schema is something like: create table group_record (id integer primary key, group_id integer, data text); (plus other tables) The transaction is something like: begin; select * from group_record where group_id = x; … reads and writes to/from other tables … select * from group_record where group_id = x; -- returns results in a different order from first time commit; Needless to say, this is one of those fun rare bugs that appeared occasionally in the logs in our production server, and we couldn't seem to reproduce it in development. Thanks! Evan Jones -- Work: https://www.mitro.co/Personal: http://evanjones.ca/ -- 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] Reasons to reorder results *within* a transaction?
Evan Jones wrote: > I *know* that without an ORDER BY clause, the database is free to reorder > results in any way it likes. However, I recently ran into a case where the > *SAME* query was returning results in a different order *within* a single > transaction, which surprised me (we fixed it by adding the missing ORDER BY). > I > would assume that once a transaction obtains a snapshot, all its read > operations > would return the same results. That is not a valid assumption. For one thing, the default transaction isolation level is read committed, and at that isolation level you are not guaranteed to even get the same *rows* running the same query twice within the same transaction, much less in the same order. At any isolation level statistics could change, resulting in a different plan on two successive executions. Even running the same plan using the same snapshot you could get a different order if you have not specified one with ORDER BY. As one example, a sequential scan of a table won't necessarily start at the beginning of the heap -- if there is already a sequential scan in progress for another process, the new one will start at the point the other one is at, and "wrap around". This can save a lot of physical disk access, resulting in better performance. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Reasons to reorder results *within* a transaction?
On Oct 4, 2013, at 13:03 , Kevin Grittner wrote: > That is not a valid assumption. For one thing, the default > transaction isolation level is read committed, and at that > isolation level you are not guaranteed to even get the same *rows* > running the same query twice within the same transaction, much less > in the same order. I guess I should have mentioned that we are using serializable snapshot isolation (thanks for that, BTW!) > if there is already a sequential > scan in progress for another process, the new one will start at the > point the other one is at, and "wrap around". This can save a lot > of physical disk access, resulting in better performance. OH! This totally, totally makes sense. This is *exactly* the kind of thing I was looking for, and I'll bet that is exactly what was happening in our case. The table is pretty small, so Postgres explain says it is doing a full table scan for this query. Thanks for the speedy insightful answer! This is yet another example of something that when tracking down the bug, we knew immediately it was incorrect and probably wrong, but sometimes you don't notice these things the first time. The joys of software. Evan -- Work: https://www.mitro.co/Personal: http://evanjones.ca/ -- 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] Reasons to reorder results *within* a transaction?
Evan Jones wrote: > I *know* that without an ORDER BY clause, the database is free to > reorder results in any way it likes. However, I recently ran into a > case where the *SAME* query was returning results in a different order > *within* a single transaction, which surprised me (we fixed it by > adding the missing ORDER BY). I would assume that once a transaction > obtains a snapshot, all its read operations would return the same > results. Yes, the same results -- not necessarily in the same order. For instance, a synchronized scan might start at a different point of the table. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, 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
