[GENERAL] Streaming replication and temp table operations
Hi all, Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master to standby servers via the new PG WAL shipping replication? Given that temp tables are only accessible per-session it would seem unnecessary but if the shipping is happening at the WAL log level is that sort of thing considered? Specifically I've got a table that I want to get some representative statistics and explain plans on prior to making it live on a production environment and so I was considering creating a temp table to populate with a sizable chunk of representative test data on the master database installation. The Streaming Replication docs ( 25.2.5 ) state: If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up. Which, in the streaming replication w/o file-based continuous archiving scenario, seems like I'd want to be absolutely certain that this setting was big enough to handle whatever data was being imported into the temp table via a COPY...FROM even if the actual table wasn't being replicated. Does anyone know if this is a valid concern and whether or not the temp table will be replicated (regardless of the use of file-based continuous archiving)? Thanks in advance, Joel -- 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] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
Thanks for the help with that, Noah. Indeed the sizes do look like I'd expect them to if I force deflation of the bytea value before inserting it into the EXTENDED column. On Apr 21, 2011, at 2:02 PM, Noah Misch wrote: On Mon, Apr 11, 2011 at 03:19:23PM -0700, Joel Stevenson wrote: create table obj1 ( object bytea ); create table obj2 ( object bytea ); alter table obj2 alter column object set storage external; insert into obj1 ( object ) select object from serialized_content where id = 12345; insert into obj2 ( object ) select object from obj1; If the value that shows up for insertion is already compressed, EXTERNAL storage will not decompress it. Change this line to insert into obj2 ( object ) select object || '' from obj1; to observe the effect you seek. Given the purpose of EXTERNAL storage, this might qualify as a bug. -- 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] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
On Apr 12, 2011, at 10:33 AM, Bill Moran wrote:
In response to Joel Stevenson jsteven...@bepress.com:
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
---+-+---+-
40960 | 32768 | 40960 | 32768
I'm not an expert, but it looks like you're not storing enough data to
actually see the difference, since the actual sizes of the tables will
always be rounded to an even page size. With only 1 row, it's always
going to take a minimum amount.
Also, are you sure you're storing compressible data? For example, if
you're putting PNG or JPEG images in there, they're not going to compress
any.
Thanks for the reply, Bill.
The data is very compressible, the raw data is 33392 bytes long and gzips down
to 6965 bytes. As far as not storing enough, the description of the 'SET
STORAGE' clause and the TOAST strategy it sounds like the TOASTer will try to
compress anything that doesn't fit into the PG page ( default 8Kb ) so I
would've thought that compression would be used for the EXTENDED column and not
used for the EXTERNAL column since my single-row data is larger than that.
To be certain I stored 10 rows of that data and rechecked the reported size
after a vacuum full:
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
+-++-
147456 | 139264 | 147456 | 139264
So, again from the outside, the column storage settings don't appear to be
behaving as I'd expect them too.
Stumped.
- Joel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
Hi all,
I'm trying to do some comparisons between the EXTERNAL and the EXTENDED storage
methods on a bytea column and from the outside the setting doesn't appear to
affect the value stored on initial insert, but perhaps I'm looking at the wrong
numbers. If I create two new tables with a single bytea column and set one of
them to external storage, then insert an existing bytea value from another
table into each one, they appear to be of exactly the same size. This is using
PG 9.0.3 on Debian Lenny, using the backports-sloppy deb package of PG 9.
(I've verified that the first table has extended storage via pg_attribute and
that the second table has external.)
create table obj1 ( object bytea );
create table obj2 ( object bytea );
alter table obj2 alter column object set storage external;
insert into obj1 ( object ) select object from serialized_content where id =
12345;
insert into obj2 ( object ) select object from obj1;
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
---+-+---+-
65536 | 57344 | 65536 | 57344
(1 row)
Now at this point if I perform a vacuum full on one or both, they'll both
shrink down to a bit over half that size:
vacuum full obj1;
vacuum full obj2;
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
---+-+---+-
40960 | 32768 | 40960 | 32768
This shrinkage doesn't occur with a normal vacuum, in fact the reported sizes
increase if I do a normal vacuum at that point, but that's not germane to my
question AFAICT.
Can I use the relation size like this to determine whether or not compression
is happening for these toast columns? If not, is there a way that I can
confirm that it is or isn't active? The results appear to be similar for text
columns.
Thanks,
Joel
--
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] Trying to get postgres to use an index
At 10:11 PM +0100 11/6/04, Pierre-Frédéric Caillaud wrote: explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID; QUERY PLAN -- Hash Join (cost=47162.85..76291.32 rows=223672 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=42415.28..42415.28 rows=741028 width=4) - Seq Scan on item i (cost=0.00..42415.28 rows=741028 width=4) This query takes about 20 seconds to run. Well, you're joining the entire two tables, so yes, the seq scan might be faster. Try your query with enable_seqscan=0 so it'll use an index scan and compare the times. You may be surprised to find that the planner has indeed made the right choice. This query selects 223672 rows, are you surprised it's slow ? I'm not a SQL guru by any stretch but would a constrained sub-select be appropriate here? e.g. a simple test setup where each record in table test1 has a FK referenced to an entry in test: joels=# \d test Table public.test Column | Type | Modifiers +--+--- id | integer | not null foo| character(3) | Indexes: test_pkey primary key, btree (id) joels=# \d test1 Table public.test1 Column | Type | Modifiers -+-+--- id | integer | not null test_id | integer | Indexes: test1_pkey primary key, btree (id) test1_test_id_idx btree (test_id) Foreign-key constraints: $1 FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE joels=# select count(*) from test; count --- 10001 (1 row) joels=# select count(*) from test1; count --- 10001 (1 row) joels=# explain select test_id from test1 t1, test t where t1.test_id = t.id; QUERY PLAN Hash Join (cost=170.01..495.05 rows=10002 width=4) Hash Cond: (outer.test_id = inner.id) - Seq Scan on test1 t1 (cost=0.00..150.01 rows=10001 width=4) - Hash (cost=145.01..145.01 rows=10001 width=4) - Seq Scan on test t (cost=0.00..145.01 rows=10001 width=4) (5 rows) joels=# explain select test_id from test1 t1 where test_id in (select id from test where id = t1.test_id); QUERY PLAN -- Seq Scan on test1 t1 (cost=0.00..15269.02 rows=5001 width=4) Filter: (subplan) SubPlan - Index Scan using test_pkey on test (cost=0.00..3.01 rows=2 width=4) Index Cond: (id = $0) (5 rows) So with the subselect the query planner would use the primary key index on test when finding referencing records in the test1 table. Pierre, I seen the advice to use an additional where condition in certain cases to induce an index scan; how is this done? my 1.2 pennies, -Joel ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Explicit column naming on INSERT question
Buenas tardes, all. I'm puzzled by my inability to use the following INSERT statement: INSERT INTO RM_USER VALUES(RM_USER.ID, RM_USER.FIRST_NAME) VALUES(1, 'DUBYA') it seems that postgres is unhappy about the explicit column naming (e.g. 'RM_USER.ID' instead of just 'ID') because I end up with either: ERROR: parser: parse error at or near values or ERROR: parser: parse error at or near . Take out the explicit table name and everything is fine. The real problem is that I've got a significant amount of code that uses this explicit naming scheme (whether it's really the best thing to do or not, that's the way it is now) and oh what a headache I'll have if I have to go through it all and change this. I'm running 7.0.2, does anybody know if this is also the case in any more recent versions of postgres? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
