Re: [GENERAL] Efficient sorting the results of a join, without denormalization
Hi Glen: On Sun, May 31, 2015 at 6:43 AM, Glen M. Witherington g...@fea.st wrote: On Sat, May 30, 2015, at 11:33 PM, David G. Johnston wrote: This is one problem with using made up surrogate keys... The PK of A is a component of both the PK of B and the PK of C but you throw that information away by using serial fields for PKs instead. You should have unique indexes on B and C that incorporate the ID from A That is quite a strange schema, though isn't it? If you imagine it as emails: C = Emails B = Folder A = User Now you're suggesting that even though an email belongs to to a folder, which belongs to a user ... each email should also contain contain a reference to a user? I guess that's fine, but seems unideal from a redundancy perspective It may seem, and be, unideal from a redundancy perspective, but keys are more natural. It means you have user (Glen), folder (Glen, PGlist) and message (Glen,PGlist,27), different from (Glen,Inbox,27) or (Glen, PgList,28) or (Francisco,PgList,27) ( Where the 'tuples' I've printed are the PK values ). This has a lot of advantages, which you pay for in other ways, like redundancies, but having composite primary keys sometimes work in your favor as you can express restrictions with the relationships and build composite indexes for add hoc queries. In this case ( an email database ), a serial could be used ( instead of the name ) for the user and folder PK, but still have very fast, simple queries from a MUA for things like 'select * from messages where user_id = Prefetched_id and not read order by timestamp desc limit 100'. Also it will help catch things like mismatching folder ids, or using the user id as folder id, which are easily made when all the keys are synthetic and meaningless numbers. As an example, I have a currency table, with it's serial key currency_id, and a seller table, which sells just a currency and whose pk is (currency_id+seller_id), and a rate table with rates (currency_id, rate_id), and an allowed rates table ( to see which rates a seller can use ), with primay key (currency_id, seller_id, rate_id) and foreign keys (currency_id, seller_id) and (currency_id, rate_id) ( it is more or less a classical example. The composite keys guarantee I can only allow a seller to sell rates on her currency. I can also, if needed, build unique indexes on any single id ( they are all serials, as I have no other candidate keys ), if I need them, but given the access patterns I normally have all of them, and things like populating a drop box to allow new rates for a seller are very easy. 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
Re: [GENERAL] Efficient sorting the results of a join, without denormalization
On Sun, 31 May 2015 04:50:00 -0500 Glen M. Witherington g...@fea.st wrote: On Sun, May 31, 2015, at 12:53 AM, Tom Lane wrote: Glen M. Witherington g...@fea.st writes: And here's the query I want to do, efficiently: SELECT * FROM c JOIN b ON b.id = c.b_id JOIN a ON a.id = b.a_id WHERE a.id = 3 ORDER BY b.created_at DESC LIMIT 10 At least for that dummy data, this seems sufficient: regression=# create index on b (a_id, created_at); CREATE INDEX regression=# explain analyze SELECT * FROM c JOIN b ON b.id = c.b_id JOIN a ON a.id = b.a_id WHERE a.id = 3 ORDER BY b.created_at DESC LIMIT 10; QUERY PLAN -- Limit (cost=0.14..21.95 rows=10 width=64) (actual time=0.064..1.176 rows=10 loops=1) - Nested Loop (cost=0.14..436079.81 rows=20 width=64) (actual time=0.063..1.173 rows=10 loops=1) Join Filter: (b.id = c.b_id) Rows Removed by Join Filter: 1218 - Nested Loop (cost=0.14..9.81 rows=20 width=40) (actual time=0.035..0.035 rows=1 loops=1) - Index Scan Backward using b_a_id_created_at_idx on b (cost=0.14..8.49 rows=20 width=24) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (a_id = 3) - Materialize (cost=0.00..1.07 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1) - Seq Scan on a (cost=0.00..1.06 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1) Filter: (id = 3) Rows Removed by Filter: 2 - Materialize (cost=0.00..27230.00 rows=100 width=24) (actual time=0.008..0.811 rows=1228 loops=1) - Seq Scan on c (cost=0.00..16370.00 rows=100 width=24) (actual time=0.007..0.310 rows=1228 loops=1) Planning time: 0.796 ms Execution time: 1.390 ms (15 rows) regards, tom lane Wow, sorry I screwed up the query. It should be: ORDER BY c.created_at DESC Not b, or as you noted its trivial to index. Sorry! Creating an index on c.created_at sped things up by a factor of over 1000, which caused the case you defined to run in ~0.5ms for me. -- Bill Moran wmo...@potentialtech.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] Curious case of huge simple btree indexes bloat.
Maxim Boguk maxim.bo...@gmail.com writes: On the one of databases under my support I found very curious case of the almost endless index bloat (index size stabilises around 100x of the original size). The table have 5 indexes and they all have the same bloating behaviour (growth to almost 100x and stabilisation around that amount). An original index size 4-8Mb (after manual reindex), over time of the 5 days they all monotonically growth to 300-900MB. In the same time table size staying pretty constant at 30-50Mb (and amount of rows in the same don't vary widely and stays between 200k and 500k). At least for the index you gave stats for, it seems like it's stabilizing at one index entry per page. This is a known possible pathological behavior if the application's usage involves heavy decimation of original entries; say, you insert sequential timestamps and then later remove all but every one-thousandth one, leaving at most one live entry on every index page. Btree can recover the totally-empty leaf pages but it has no provision for merging non-empty leaf pages, so those all stay as they are indefinitely. It would be pretty unusual for all the indexes on a table to be used like that, though. 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] How to retrieve Comment text using SQL, not psql?
Hi Bob: On Sat, May 30, 2015 at 1:48 PM, Bob Futrelle bob.futre...@gmail.com wrote: Using pgAdmin3 I've tried this and variations on it. All are rejected. select COMMENT ON TABLE articlestats pgAdmin3 is a bit complex for me, bot on vanilla psql you can use the switch: -E --echo-hidden Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on. to learn how it does them, and elaborate from there, Its a nice resource ( although I fear its queries evolve with versions, but you can always port them touse the information_schema ( http://www.postgresql.org/docs/9.4/static/information-schema.html ) which should be a little more stable. 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] Help me recovery databases.
Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb) This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have archive_status folder. -- С Уважением,Генералов Юрий
Re: [GENERAL] Postgresql 9.4 upgrade openSUSE13.1
On 05/31/2015 08:57 AM, Arup Rakshit wrote: Hi, I have installed long back postgresql92, and I want to upgrade it to postgresql 9.4. But not seeing any package for that. Any help ? [arup@music_track (master)]$ zypper info postgresql94 Loading repository data... Reading installed packages... FYI for future reference I would use zypper search postgres. zypper info needs an exact name, search is more like grep -i. In any case postgresql94 is not in the default repos. I am on openSUSE 13.1 and compile my Postgres versions from source. If you want to get an RPM package I would look here(see WARNING below): http://software.opensuse.org/package/postgresql94 *WARNING* I have not done this and I do not know if it installs over the existing Postgres install or not. I would definitely backup your data and conf files. The safer route would be to try this out on another machine, say a cloud instance *WARNING* package 'postgresql94' not found. [arup@music_track (master)]$ -- 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] Efficient sorting the results of a join, without denormalization
On Sun, May 31, 2015, at 01:16 PM, Francisco Olarte wrote: It may seem, and be, unideal from a redundancy perspective, but keys are more natural. It means you have user (Glen), folder (Glen, PGlist) and message (Glen,PGlist,27), different from (Glen,Inbox,27) or (Glen, PgList,28) or (Francisco,PgList,27) ( Where the 'tuples' I've printed are the PK values ). This has a lot of advantages, which you pay for in other ways, like redundancies, but having composite primary keys sometimes work in your favor as you can express restrictions with the relationships and build composite indexes for add hoc queries. In this case ( an email database ), a serial could be used ( instead of the name ) for the user and folder PK, but still have very fast, simple queries from a MUA for things like 'select * from messages where user_id = Prefetched_id and not read order by timestamp desc limit 100'. Also it will help catch things like mismatching folder ids, or using the user id as folder id, which are easily made when all the keys are synthetic and meaningless numbers. As an example, I have a currency table, with it's serial key currency_id, and a seller table, which sells just a currency and whose pk is (currency_id+seller_id), and a rate table with rates (currency_id, rate_id), and an allowed rates table ( to see which rates a seller can use ), with primay key (currency_id, seller_id, rate_id) and foreign keys (currency_id, seller_id) and (currency_id, rate_id) ( it is more or less a classical example. The composite keys guarantee I can only allow a seller to sell rates on her currency. I can also, if needed, build unique indexes on any single id ( they are all serials, as I have no other candidate keys ), if I need them, but given the access patterns I normally have all of them, and things like populating a drop box to allow new rates for a seller are very easy. Francisco Olarte. Thanks Francisco, that makes sense. I've started moving my code to that, and it eliminates all the performance issues I had. I guess I was really hoping there would exist some sort of dereference option when indexing, so I could dereference a foreign key, and then index on a attribute of that row. E.g. So I could have created an index such as: deref(deref(mail.folder_id).user_id, created_at) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 9.4 upgrade openSUSE13.1
Hi, I have installed long back postgresql92, and I want to upgrade it to postgresql 9.4. But not seeing any package for that. Any help ? [arup@music_track (master)]$ zypper info postgresql94 Loading repository data... Reading installed packages... package 'postgresql94' not found. [arup@music_track (master)]$ -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- 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] Help me recovery databases.
If you have a pg_dumpall, or a pg_dump of your databases, you might be able to get your data back by doing the following. 1. If your data directory is corrupted or still exists, rename it. 2. Make copies of your postgresql.conf pg_hba.conf if you still have them. 3. use initdb to recreate the data directory 4. Start PostgreSQL and create the database(s) you need 5, Restore your data from pg_dumpall or pg_dump's. 6. If step 5 works, replace the new pg_hba.conf with the old copy if you have it. On Sun, May 31, 2015 at 7:38 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: base is where all the data files are located, so the answer is most likely 'no'. On 05/31/15 15:11, Evi-M wrote: Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb) This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have archive_status folder. -- С Уважением,Генералов Юрий -- Tomas Vondra http://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 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Help me recovery databases.
base is where all the data files are located, so the answer is most likely 'no'. On 05/31/15 15:11, Evi-M wrote: Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb) This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have archive_status folder. -- С Уважением,Генералов Юрий -- Tomas Vondra http://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
Re: [GENERAL] replacing jsonb field value
On Sat, May 30, 2015 at 9:10 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Michael Paquier michael.paqu...@gmail.com wrote:
Append the new value to it the existing field, jsonb has as property
to enforce key uniqueness, and uses the last value scanned for a given
key.
can you show a simple example, how to append a jsonb to an jsonb-field?
Maybe i'm blind, but i can't find how it works.
You need some extra magic to do it in 9.4, for example that (not the
best performer by far that's simple enough):
=# CREATE FUNCTION jsonb_append(jsonb, jsonb)
RETURNS jsonb AS $$
WITH json_union AS
(SELECT * FROM jsonb_each_text($1)
UNION ALL
SELECT * FROM jsonb_each_text($2))
SELECT json_object_agg(key, value)::jsonb FROM json_union;
$$ LANGUAGE SQL;
CREATE FUNCTION
=# SELECT jsonb_append('{a1:v1, a2:v2}', '{a1:b1}');
jsonb_append
--
{a1: b1, a2: v2}
(1 row)
Googling would show up more performant functions for sure, usable with
9.4, and there is even jsonbx.
--
Michael
--
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] JSONB matching element count
On Sun, May 31, 2015 at 11:07 PM, Arup Rakshit
arupraks...@rocketmail.com wrote:
Hi,
This says if matched found or not against the input array :
'{a:1, b:2, c:3}'::jsonb ?| array['b', 'c']
But how would I determine how many matched ? Like for the above example, I
see only 2 matched found.
You could use ? to do the check for each individual key, and then
count how many matched...
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JSONB matching element count
Hi,
This says if matched found or not against the input array :
'{a:1, b:2, c:3}'::jsonb ?| array['b', 'c']
But how would I determine how many matched ? Like for the above example, I see
only 2 matched found.
--
Regards,
Arup Rakshit
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.
--Brian Kernighan
--
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] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
On Fri, May 29, 2015 at 10:37:57AM +1200, Thomas Munro wrote: On Fri, May 29, 2015 at 7:56 AM, Robert Haas robertmh...@gmail.com wrote: - There's a third possible problem related to boundary cases in SlruScanDirCbRemoveMembers, but I don't understand that one well enough to explain it. Maybe Thomas can jump in here and explain the concern. I noticed something in passing which is probably not harmful, and not relevant to this bug report, it was just a bit confusing while testing: SlruScanDirCbRemoveMembers never deletes any files if rangeStart == rangeEnd. In practice, if you have an idle cluster with a lot of multixact data and you VACUUM FREEZE all databases and then CHECKPOINT, you might be surprised to see no member files going away quite yet, but they'll eventually be truncated by a future checkpoint, once rangeEnd has had a chance to advance to the next page due to more multixacts being created. If we want to fix this one day, maybe the right thing to do is to treat the rangeStart == rangeEnd case the same way we treat rangeStart rangeEnd, that is, to assume that the range of pages isn't wrapped/inverted in this case. I agree. Because we round rangeStart down to a segment boundary, oldest and next member offsets falling on the same page typically implies rangeStartrangeEnd. Only when the page they share happens to be the first page of a segment does one observe rangeStart==RangeEnd. While testing this (with inconsistent-multixact-fix-master.patch applied, FWIW), I noticed a nearby bug with a similar symptom. TruncateMultiXact() omits the nextMXact==oldestMXact special case found in each other find_multixact_start() caller, so it reads the offset of a not-yet-created MultiXactId. The usual outcome is to get rangeStart==0, so we truncate less than we could. This can't make us truncate excessively, because nextMXact==oldestMXact implies no table contains any mxid. If nextMXact happens to be the first of a segment, an error is possible. Procedure: 1. Make a fresh cluster. 2. UPDATE pg_database SET datallowconn = true 3. Consume precisely 131071 mxids. Number of offsets per mxid is unimportant. 4. vacuumdb --freeze --all Expected state after those steps: $ pg_controldata | grep NextMultiXactId Latest checkpoint's NextMultiXactId: 131072 Checkpoint will fail like this: 26699 2015-05-31 17:22:33.134 GMT LOG: statement: checkpoint 26661 2015-05-31 17:22:33.134 GMT DEBUG: performing replication slot checkpoint 26661 2015-05-31 17:22:33.136 GMT ERROR: could not access status of transaction 131072 26661 2015-05-31 17:22:33.136 GMT DETAIL: Could not open file pg_multixact/offsets/0002: No such file or directory. 26699 2015-05-31 17:22:33.234 GMT ERROR: checkpoint request failed 26699 2015-05-31 17:22:33.234 GMT HINT: Consult recent messages in the server log for details. 26699 2015-05-31 17:22:33.234 GMT STATEMENT: checkpoint This does not block startup, and creating one mxid hides the problem again. Thus, it is not a top-priority bug like some other parts of this thread. I mention it today mostly so it doesn't surprise hackers testing other fixes. Thanks, nm -- 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] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
Incomplete review, done in a relative rush:
On Fri, May 29, 2015 at 03:08:11PM -0400, Robert Haas wrote:
OK, here's a patch. Actually two patches, differing only in
whitespace, for 9.3 and for master (ha!). I now think that the root
of the problem here is that DetermineSafeOldestOffset() and
SetMultiXactIdLimit() were largely ignorant of the possibility that
they might be called at points in time when the cluster was
inconsistent.
A cause perhaps closer to the root is commit f741300 moving truncation from
VACUUM to checkpoints. CLOG has given us deep experience with VACUUM-time
truncation. Commit f6a6c46d and this patch are about bringing CHECKPOINT-time
truncation up to the same level.
Speaking of commit f6a6c46d, it seems logical that updating allocation stop
limits should happen proximate to truncation. That's currently the case for
CLOG (vac_truncate_clog() does both) and pg_multixact/members (checkpoint's
TruncateMultiXact() call does both). However, pg_multixact/offsets is
truncated from TruncateMultiXact(), but vac_truncate_clog() updates its limit.
I did not distill an errant test case, but this is fishy.
SetMultiXactIdLimit() bracketed certain parts of its
logic with if (!InRecovery), but those guards were ineffective because
it gets called before InRecovery is set in the first place.
SetTransactionIdLimit() checks InRecovery for the same things, and it is
called at nearly the same moments as SetMultiXactIdLimit(). Do you have a
sense of whether it is subject to similar problems as a result?
1. Moves the call to DetermineSafeOldestOffset() that appears in
StartupMultiXact() to TrimMultiXact(), so that we don't try to do this
until we're consistent. Also, instead of passing
MultiXactState-oldestMultiXactId, pass the newer of that value and
the earliest offset that exists on disk. That way, it won't try to
read data that's not there.
Perhaps emit a LOG message when we do that, since it's our last opportunity to
point to the potential data loss?
+ * PostgreSQL 9.3.0 through 9.3.6 and PostgreSQL 9.4.0 through 9.4.1
+ * had bugs that could allow users who reached those release through
s/release/releases/
@@ -2859,6 +2947,14 @@ TruncateMultiXact(void)
SimpleLruTruncate(MultiXactOffsetCtl,
MultiXactIdToOffsetPage(oldestMXact));
+ /* Update oldest-on-disk value in shared memory. */
+ earliest = range.rangeStart * MULTIXACT_OFFSETS_PER_PAGE;
+ if (earliest FirstMultiXactId)
+ earliest = FirstMultiXactId;
+ LWLockAcquire(MultiXactGenLock, LW_EXCLUSIVE);
+ Assert(MultiXactState-oldestMultiXactOnDiskValid);
+ MultiXactState-oldestMultiXactOnDiskValid = earliest;
That last line needs s/Valid//, I presume. Is it okay that
oldestMultiXactOnDisk becomes too-old during TruncateMultiXact(), despite its
Valid indicator remaining true?
+static MultiXactOffset
+GetOldestMultiXactOnDisk(void)
+{
+ SlruScanDirectory(MultiXactOffsetCtl, SlruScanDirCbFindEarliest,
trunc);
+ earliest = trunc.earliestExistingPage * MULTIXACT_OFFSETS_PER_PAGE;
+ if (earliest FirstMultiXactId)
+ earliest = FirstMultiXactId;
SlruScanDirCbFindEarliest() is only meaningful if the files on disk do not
represent a wrapped state. When the files do represent a wrapped state,
MultiXactIdPrecedes() is not transitive, and the SlruScanDirCbFindEarliest()
result is sensitive to readdir() order. This code exists specifically to help
us make do in the face of wrong catalog and pg_control entries. We may have
wrapped as a result of those of same catalog/pg_control entries, so I think
this function ought to account for wrap. I haven't given enough thought to
what exactly it should do.
Thanks,
nm
--
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] replacing jsonb field value
Glyn Astill glynast...@yahoo.co.uk wrote: Prior to 9.5 you can't, I think you have to use something like jsonb_each to unwrap it then wrap it back up again. The jsonbx extension, which I believe is what ended up in 9.5 has a simple concatenate function (here: https://github.com/erthalion/jsonbx), I also had a go (here: https://github.com/glynastill/pg_jsonb_opx). Thanks. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Efficient sorting the results of a join, without denormalization
On Sun, May 31, 2015, at 12:53 AM, Tom Lane wrote: Glen M. Witherington g...@fea.st writes: And here's the query I want to do, efficiently: SELECT * FROM c JOIN b ON b.id = c.b_id JOIN a ON a.id = b.a_id WHERE a.id = 3 ORDER BY b.created_at DESC LIMIT 10 At least for that dummy data, this seems sufficient: regression=# create index on b (a_id, created_at); CREATE INDEX regression=# explain analyze SELECT * FROM c JOIN b ON b.id = c.b_id JOIN a ON a.id = b.a_id WHERE a.id = 3 ORDER BY b.created_at DESC LIMIT 10; QUERY PLAN -- Limit (cost=0.14..21.95 rows=10 width=64) (actual time=0.064..1.176 rows=10 loops=1) - Nested Loop (cost=0.14..436079.81 rows=20 width=64) (actual time=0.063..1.173 rows=10 loops=1) Join Filter: (b.id = c.b_id) Rows Removed by Join Filter: 1218 - Nested Loop (cost=0.14..9.81 rows=20 width=40) (actual time=0.035..0.035 rows=1 loops=1) - Index Scan Backward using b_a_id_created_at_idx on b (cost=0.14..8.49 rows=20 width=24) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (a_id = 3) - Materialize (cost=0.00..1.07 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1) - Seq Scan on a (cost=0.00..1.06 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1) Filter: (id = 3) Rows Removed by Filter: 2 - Materialize (cost=0.00..27230.00 rows=100 width=24) (actual time=0.008..0.811 rows=1228 loops=1) - Seq Scan on c (cost=0.00..16370.00 rows=100 width=24) (actual time=0.007..0.310 rows=1228 loops=1) Planning time: 0.796 ms Execution time: 1.390 ms (15 rows) regards, tom lane Wow, sorry I screwed up the query. It should be: ORDER BY c.created_at DESC Not b, or as you noted its trivial to index. Sorry! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] advocating LTS release and feature-train release cycles
My comments advocating a (ubuntu/debian/linux-kernel/firefox) LTS release and feature-train release cycle: https://lwn.net/Articles/646740/ https://lwn.net/Articles/646743/ The parent article PostgreSQL: the good, the bad, and the ugly: https://lwn.net/Articles/645020/ My summary (from one of my comments above): For PostgreSQL may be: - normal release every 3 or 4 months - LTS release every 12, 18 or 24 months This model provides: - higher frequency normal releases to a) showcase new features to the public and b) reduce pressure on developers wanting to not miss an infrequent annual release; and - lower frequency LTS releases to a) focus testing, stability and long term support resources b) satisfy conservative/ enterprise RDBMS admins Regards, Zenaan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] Re: [GENERAL] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
On Sat, May 30, 2015 at 8:55 PM, Andres Freund and...@anarazel.de wrote: Is oldestMulti, nextMulti - 1 really suitable for this? Are both actually guaranteed to exist in the offsets slru and be valid? Hm. I guess you intend to simply truncate everything else, but just in offsets? oldestMulti in theory is the right thing, I think, but in actuality we know that some people have 1 here instead of the correct value. One argument against this idea is that we may not want to keep a full set of member files on standbys (due to disk space usage), but that's what will happen unless we truncate during replay. I think that argument is pretty much the death-knell.= Yes. Truncating on the standby is really not optional. I think at least for 9.5+ we should a) invent proper truncation records for pg_multixact b) start storing oldestValidMultiOffset in pg_control. The current hack of scanning the directories to get knowledge we should have is a pretty bad hack, and we should not continue using it forever. I think we might end up needing to do a) even in the backbranches. Definitely agree with WAL-logging truncations; also +1 on backpatching that to 9.3. We already have experience with adding extra WAL records on minor releases, and it didn't seem to have bitten too hard. I'm inclined to agree. My only problem is that I'm not sure whether we can find a way of doing all this without adding a pg_control field. Let me try to sketch this out: 1) We continue determining the oldest SlruScanDirectory(SlruScanDirCbFindEarliest) on the master to find the oldest offsets segment to truncate. Alternatively, if we determine it to be safe, we could use oldestMulti to find that. 2) SlruScanDirCbRemoveMembers is changed to return the range of members to remove, instead of doing itself 3) We wal log [oldest offset segment guaranteed to not be alive, nextmulti) for offsets, and [oldest members segment guaranteed to not be alive, nextmultioff), and redo truncations for the entire range during recovery. I'm pretty tired right now, but this sounds doable. I'm probably biased here, but I think we should finish reviewing, testing, and committing my patch before we embark on designing this. So far we have no reports of trouble attributable to the lack of the WAL-logging support discussed here, as opposed to several reports of trouble from the status quo within days of release. I'm having trouble reconstructing the series of events where what you're worried about here really becomes a problem, and I think we ought to have a very clear idea about that before back-patching changes of this type. It's true that if the state of the SLRU directory is in the future, because recovery was restarted from an earlier checkpoint, we might replay a checkpoint and remove some of those files from the future. But so what? By the time we've reached the minimum recovery point, they will have been recreated by the same WAL records that created them in the first place. If, in the previous replay, we had wrapped all the way around, some of the stuff we keep may actually already have been overwritten by future WAL records, but they'll be overwritten again. Now, that could mess up our determination of which members to remove, I guess, but I'm not clear that actually matters either: if the offsets space has wrapped around, the members space will certainly have wrapped around as well, so we can remove anything we like at this stage and we're still OK. I agree this is ugly the way it is, but where is the actual bug? As far as your actual outline goes, I think if we do this, we need to be very careful about step #2. Right now, we decide what we need to keep and then remove everything else, but that's kind of wonky because new stuff may be getting created at the same time, so we keep adjusting our idea of exactly what needs to be removed. It would be far better to invert that logic: decide what needs to be removed - presumably, everything from the oldest member that now exists up until some later point - and then remove precisely that stuff and nothing else. -- Robert Haas EnterpriseDB: 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] date type changing to timestamp without time zone in postgres 9.4
On 05/30/2015 10:05 PM, Rishi Gokhale wrote: When I create a table with a column whose type is date the type gets forced to timestamp without timezone after it gets created ops=# CREATE TABLE test ( ops(# namevarchar(40) NOT NULL, ops(# start date NOT NULL ops(# ); CREATE TABLE ops=# \d test; Table public.test Column |Type | Modifiers +-+--- name | character varying(40) | not null start | timestamp without time zone | not null ops=# The table creation is just a test, my original issue is while restoring a backup (pg_dump/pg_restore) from another server also 9.4, where the date types on numerous columns get forced to change to timestamp without timezone. Any help would be appreciated. Not seeing that here: test=# select version(); version - PostgreSQL 9.4.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit (1 row) test=# create table date_test(dt_fld date); CREATE TABLE test=# \d date_test Table public.date_test Column | Type | Modifiers +--+--- dt_fld | date | Sure someone has not overridden the date type in your installation? See what \dT or \dD return? Thanks, Rishi -- 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
