[GENERAL] 9.4's limited logical replication, anyone actually used it, yet?

If so, I’d love any pointers or gotchas that it took doing to work out. -- 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] [postgresql 9.3.5] autovacuums stuck on non-existent pg_toast tables

On Feb 25, 2015, at 3:27 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Tong Pham wrote: We do have fsync turned on, and there was no disk failure. The database had to be shut down forcefully because it was becoming nonresponsive (probably due to inadequate earlier vacuuming) and we

[GENERAL] Temp files and process memory footprint

Looking to start collecting what memory usage I can via logging with an eye towards establishing typical usage profiles for different users (i.e. app, reporting, etc.) and hopefully later gaining the ability to kill extreme outliers when they hit. So, would the temp file size logged via

Re: [GENERAL] Age of the WAL?

On Mar 12, 2013, at 4:13 PM, Tom Lane wrote: Erik Jones ejo...@engineyard.com writes: What's the best way to determine the age of the current WAL? Not the current segment, but the whole thing. Put another way: is there a way to determine a timestamp for the oldest available transaction

[GENERAL] Age of the WAL?

What's the best way to determine the age of the current WAL? Not the current segment, but the whole thing. Put another way: is there a way to determine a timestamp for the oldest available transaction in the WAL? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Recovering data from table show corruption with invalid page header in block X

On Feb 9, 2010, at 3:28 PM, Erik Jones wrote: Greetings, We've recently had database server crash due to a heavy duty disk failure and upon rebooting we now have a table showing corruption via the invalid page header in block X message when querying one table in particular, the rest

[GENERAL] Recovering data from table show corruption with invalid page header in block X

to get those damaged pages cleared out... Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Recovering data from table show corruption with invalid page header in block X

On Feb 9, 2010, at 5:00 PM, Jeff Davis wrote: On Tue, 2010-02-09 at 15:28 -0800, Erik Jones wrote: * Set zero_damaged_pages=on, run query that originally showed the corruption. This reports 3 different blocks with invalid page headers and reports that they are being zero'd out

[GENERAL] MySQL - Postgres migration tools?

on pgfoundry only turns up a few projects that haven't seen any recent updates... Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] WAL Log Shipping - Warm Standby not working under 8.3.7

deleting WAL files that are needed for the standby -- it needs all WAL files generated from the start_backup() call on. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing

Re: [GENERAL] WAL Log Shipping - Warm Standby not working under 8.3.7

${slave_backup_path}/0* 2/dev/null ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l logfile start Is ${slave_backup_path} your archive directory? Why are you deleting all of you archives there? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability

Re: [GENERAL] WAL Log Shipping - Warm Standby not working under 8.3.7

On Jan 8, 2010, at 4:50 PM, Erik Jones wrote: On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote: OK, So what am I doing wrong here? Installed PG 8.3.7 on Slave machine Restored from last evening's backup from the master DB to make the rsync across the network finish sooner. Shut

Re: [GENERAL] How psql source code can be protected?

. If you don't trust your hosting company then why are you doing business with them? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Innotop for postgresl

Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] Why grantor is owner in this case?

you'd need to do is wrap your GRANT statements in functions that log what was done and by whom to a table and then consult that log table when you need that info. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k

Re: [GENERAL] Justifying a PG over MySQL approach to a project

then that situation alone sounds like a good business reason to me not to be looking at MySQL right now. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Justifying a PG over MySQL approach to a project

to the table locks required by MyISAM. As you mentioned, for the type of active workloads that MyISAM is good for, you might as well just use memcache over something more reliable and/or concurrent, or even a simple key-value or document store if you really don't need transactions. Erik Jones

Re: [GENERAL] How to get text for a plpgsql variable from a file.

text; BEGIN CREATE TEMP TABLE x (x text); COPY x from '/path/to/myfile.txt'; mytxt := (SELECT x from x); ... END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; If you're allowed, you can use an untrusted procedural language. Erik Jones, Database Administrator Engine Yard Support, Scalability

[GENERAL] Easier to use warm standby in 8.4?

So, the release notes for 8.4 had Easier to use Warm Standby in them. What changes were made that make it easier to use? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing

Re: [GENERAL] XLOG's implementation details

stuff works underneath the covers. I figured asking this list would be the simplest way to figure things out. Yep :) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list

Re: [GENERAL] XLOG's implementation details

stuff works underneath the covers. I figured asking this list would be the simplest way to figure things out. Yep :) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list

Re: [GENERAL] Catalog help

constraint declared) to the oid values of rows in pg_namespace catalog table. For example, say you want to find all of the schemas with a table named 'foo': SELECT n.nspname FROM pg_namespace n, pg_class c WHERE c.relnamespace = n.oid AND c.relname = 'foo'; Erik Jones, Database

Re: [GENERAL] Is there a way to know if trigger is invoked by the code from another trigger

A. Nothing built in to show you that. You could, however, have your trigger on table A make an insert or update to a record in some table that the trigger on table B could then look for. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US

Re: [GENERAL] Inheritance on foreign key

are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via

Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...

for data integrity. Have you considered a non-relational, schema-less database such as MongoDB or Cassandra? You're pretty much throwing out the relational features of this database anyways so it seems that it would make sense to use something more geared to that kind of work. Erik Jones

Re: [GENERAL] Re: user/grant - best practices handling permission in production system

yourself to think about your design from another angle. Another way to look at permissions is that if you give too much you're just creating the risk of more work for yourself if someone later abuses them as you'll be the one asked to fix their mess. Erik Jones, Database Administrator Engine

Re: [GENERAL] Idle in transaction help

for grep: lsof -i tcp:48727 that way you keep the column headers in the output. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [Fwd: Re: [GENERAL] How to trace client sql requests?]

then it won't see it. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] PostgreSQL and Poker

the tables :) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Python client + select = locked resources???

transaction is coming from run: lsof -itcp:port where port is the value from the client_port column in the earlier pg_stat_activity output. In the lsof output there should be a pid column take the pid value and run: ps aux | grep pid substituting the actual pid value for pid. Erik Jones, Database

Re: [GENERAL]

will be null. and if I have a row in table C where c.id is null? A don't know. No, it's perfectly clear as 'NULL = NULL' evaluates to false: postgres=# select null = null; ?column? -- (1 row) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x

Re: [GENERAL]

On Jun 30, 2009, at 11:25 AM, David Fetter wrote: On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote: postgres=# select null = null; ?column? -- (1 row) Actually, it's NULL. shac...@postgres:5432=# SELECT (NULL = NULL) IS NULL; ?column? -- t (1 row) Er, yeah, I

Re: [GENERAL] partitioning question -- how to guarantee uniqueness across partitions

value, nextval('sequence_name'), for the id values then that can never happen unless you at some point use setval('sequence_name', X) where X = the max value already present in your partitioned table, which you should never be doing anyway. Erik Jones, Database Administrator Engine Yard

Re: [GENERAL] running pg_dump from python

/scholarpack.sql 2 c:/scholarpack/ancillary/ dump.err) status = p.close() Then check status to see if the command was successful or not. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general

Re: [GENERAL] Dynamic table

,NULL,NULL,10} (3 rows) Time: 0.431 ms Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] warm standby with WAL shipping

of the info needed to set things up manually. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Best way to monitor, control, or rewrite data definition commands?

rolename; to change to a role that your existing role has membership in (or any to any role for superuser roles). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list

Re: [GENERAL] Schema per user?

know how our testing goes. 1,000 is nothing in terms of schemas. You should be fine. One thing you'll notice a big change in is dump times from pg_dump when compared to what you'd see from a db with the same size data set but a smaller schema. Erik Jones, Database Administrator Engine Yard

Re: [GENERAL] Schema per user?

transparently accessing schemas regardless of what actual db their on -- and I do think that once you get up to those #s you're talking you're going to need to partition across multiple boxes. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260

Re: [GENERAL] date ranges in where

' AND lastlogin = 'Y' + '1 day'::interval Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] date ranges in where

On May 6, 2009, at 2:17 PM, Erik Jones wrote: On May 6, 2009, at 2:12 PM, Miguel Miranda wrote: Hi, what is the recommended way to select a range of dates? Lets say a have a table with a lastlogin (timestamp) column and i want toknow what users logged in for last time between 2009-05-01

Re: [GENERAL] date ranges in where

that would also match '2009-05-03 00:00:00'. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] date ranges in where

' AND lastlogin = 'Y' + '1 day'::interval it includes the 0 hours of day 3: 05-02-2009 12:00:00 AM No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01 and 2009-05-02. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US

Re: [GENERAL] date ranges in where

On May 6, 2009, at 2:55 PM, Miguel Miranda wrote: On Wed, May 6, 2009 at 3:51 PM, Erik Jones ejo...@engineyard.com wrote: On May 6, 2009, at 2:48 PM, Miguel Miranda wrote: Well, i tried all your sugestions, and i found some funny issues, i use the query to count exactly in a day by day

Re: [GENERAL] date ranges in where

On May 6, 2009, at 2:59 PM, Adrian Klaver wrote: On Wednesday 06 May 2009 2:51:08 pm Erik Jones wrote: On May 6, 2009, at 2:48 PM, Miguel Miranda wrote: Well, i tried all your sugestions, and i found some funny issues, i use the query to count exactly in a day by day basis, and running

Re: [GENERAL] Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

to be repeatable when you only need to make sure that each number is only generated once? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] triggers and execute...

with a dynamic way to use new.* so I ended up just writing out the attribute names in my partition triggers, as well. In fact, you may want to take a look at pg_partitioner. It needs some polish but most of the basic functionality is there. Erik Jones, Database Administrator Engine Yard Support

Re: [GENERAL] OperationalError: FATAL: could not open file filename: Too many open files in system

postmaste 62 saslauthd 69 sendmail 28 sh 7 sort 183 sshd 13 syslogd 8 uniq 19 xinetd I'd start by investigating the 5K+ file descriptors held by Apache. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260

Re: [GENERAL] Yet another drop table vs delete question

to TRUNCATE as it deletes the pages that are allocated to the table (I'm not sure if the TRUNCATE handles clearing out the FSM entries for that table or if VACUUM does when the table is next vacuum'd). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability

Re: [GENERAL] Can Autovaccuum also reindex

to be placed within a custom cron job? Many thanks! Eric Crowe autovacuum will kick off VACUUMs and ANALYZEs but no REINDEXes. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general

Re: [GENERAL] PITR - warm standby switchover question

:23 PM, Dan Hayes wrote: Excellent! Thanks. One other quick question... What would happen if I didn't delete the recovery.conf file? Is that step just to prevent accidentally restarting the server with it there? On Tue, Apr 14, 2009 at 6:26 PM, Erik Jones ejo...@engineyard.com wrote

Re: [GENERAL] PITR - warm standby switchover question

of information (which is bad as that's also why I never tested the alternate scenario mentioned in my other response). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list

Re: [GENERAL] PITR - warm standby switchover question

need to do is touch (create) that file and pg_standby will let the server come out of recovery mode into normal operation mode. Be sure to rm or mv the recovery.conf once that is complete. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260

Re: [GENERAL] nooby Q: temp tables good for web apps?

-N flag. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] How to capture an interactive psql session in a log file?

also allow pagila# \o allout.txt 2 and this would default to stdout for backwards compatibility (and simplicity) pagila# \o stdout.txt Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql

Re: [GENERAL] high load on server

3269 20 3 72 5 Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Last modification time of a database?

On Mar 23, 2009, at 5:00 PM, Craig Ringer wrote: Erik Jones wrote: Am I missing something obvious here? If not, has anyone come up with a reliable way to do this? Triggers on all your tables that append to a logging table? Have the client do it? Note that you do *NOT* want to have

[GENERAL] Last modification time of a database?

and the difference between the two can vary with the size of the table. Am I missing something obvious here? If not, has anyone come up with a reliable way to do this? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC

Re: [GENERAL] pg_restore error - Any Idea?

will contain a table of contents of all of the database objects in the dump file. Something in that is causing an error for pg_restore. Does the version of pg_restore match up with the version of pg_dump that you used to make the dump? Erik Jones, Database Administrator Engine Yard Support

Re: [GENERAL] [SQL] bash postgres

-+-+-- to1 | from1 | subject1 to2 | from2 | subject2 (2 rows) Time: 1.011 ms (P.S. Your quotes around $two in your original are not needed, in fact they're straight up broken as $two is already inside of a double- quoted string). Erik Jones, Database Administrator Engine

Re: [GENERAL] [SQL] bash postgres

On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: Erik Jones ejo...@engineyard.com writes: On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: How do I use \c (or any other psql commands beginning with a \) in a bash script? For multi-line input to a psql call in a bash (or any decent shell

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine

Re: [GENERAL] postgreSQL amazon ec2 cloud

-configured and, thus, not supported yet. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

' utility. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Appending \o output instead of overwriting the output file

different from the base command. Yes, also if \o already supports | why not other plumbing symbols like and for completeness (also possibly filedescriptor?) I like that. Specifying other file descriptors (e.g. 2) and redirecting output from on fd to another (#) would be nice. Erik Jones

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

On Feb 19, 2009, at 6:30 PM, R Smith wrote: On Feb 19, 2009, at 11:07 AM, SHARMILA JOTHIRAJAH wrote: Thanks all -Sharmila --- On Thu, 2/19/09, Erik Jones ejo...@engineyard.com wrote: From: Erik Jones ejo...@engineyard.com Subject: Re: [GENERAL] How to pipe the psql copy command to Unix

Re: [GENERAL] 8.3 doc issue

://www.postgresql.org/docs/8.3/interactive/pgstandby.html Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Pet Peeves?

a *ton* of IO on that db (had thousands upon thousands of tables). Also, if you do that you need to be sure to copy pgstat.stat to a permanent place periodically unless you want to risk losing all of your stats. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability

Re: [GENERAL] PGSQL or other DB?

On Feb 2, 2009, at 12:23 AM, durumdara wrote: Hi! 2009.01.31. 10:13 keltezéssel, Erik Jones írta: On Jan 30, 2009, at 11:37 AM, durumdara wrote: The main viewpoints: - quick (re)connect - because mod_python basically not store the database connections persistently mod_python

Re: [GENERAL] Warm Standby question

of a file into the pg_xlog directory needs to be atomic and there's no guarantee that any given archive_command will use a tool that does atomic copies. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k

Re: [GENERAL] PGSQL or other DB?

On Jan 31, 2009, at 9:36 AM, Scott Marlowe wrote: On Sat, Jan 31, 2009 at 2:13 AM, Erik Jones ejo...@engineyard.com wrote: On Jan 30, 2009, at 11:37 AM, durumdara wrote: - I can add/modify a table, or a field to a table without full lock on the table (like DBISAM restructure). Like

Re: [GENERAL] PGSQL or other DB?

(what kind of IO subsystem? how many concurrent connections do you need to support? how much memory do you have? how large is our data set? etc...) than you will answers. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific

Re: [GENERAL] Pet Peeves?

to generate full create statements for database objects via an SQL command. I.e. shelling out to pg_dump is not always a fun option. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql

Re: [GENERAL] Inheritance question

, sorting each along the way, the results from each child could be appended and maintain ordering of the results but the planner has no idea of anything like that. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC

Re: [GENERAL] Description of transaction model for indexes

in the event of a HOT updated tuple. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] lack of consequence with domains and types

On Dec 22, 2008, at 1:08 PM, Grzegorz Jaśkiewicz wrote: On Mon, Dec 22, 2008 at 6:10 PM, Erik Jones ejo...@engineyard.com wrote: As mentioned above, by fixing the behavior to be what you're expecting you'd be breaking the defined behavior of ALTER TABLE. I don't understand. The domain's

Re: [GENERAL] lack of consequence with domains and types

On Dec 24, 2008, at 12:04 PM, Grzegorz Jaśkiewicz wrote: On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones ejo...@engineyard.com wrote: Yes, and columns have default values, too, which are not tied to their datatype's default value (if it even has one). ALTER TABLE initializes rows to have

Re: [GENERAL] lack of consequence with domains and types

be breaking the defined behavior of ALTER TABLE. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Mirroring existing mysql setup

, that takes two passes over the data instead of one so there's a bigger IO hit. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Possible bug with ALTER LANGUAGE ... OWNER TO ...

:) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] Possible bug with ALTER LANGUAGE ... OWNER TO ...

On Dec 8, 2008, at 1:42 PM, Tom Lane wrote: Erik Jones [EMAIL PROTECTED] writes: I've just run up against a problem with ALTER LANGUAGE ... OWNER TO ... wherein the change of ownership does not propagate to a language's handler and validator functions preventing you from dropping the role

[GENERAL] Possible bug with ALTER LANGUAGE ... OWNER TO ...

cannot be dropped because some objects depend on it DETAIL: owner of function plpgsql_validator(oid) owner of function plpgsql_call_handler() Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql

Re: [GENERAL] No serial type

on that which may in turn surprise others. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Upgrading side by side in Gentoo

running cutting edge software, poppycock. Any self-respecting company running Gentoo should be maintaining their own portage build servers just as a Debian based company would maintain their own build servers for apt or RedHat/CentOS for rpm/yum. Erik Jones, Database Administrator Engine Yard

Re: [GENERAL] Upgrading Postgres question

master/ slave replication but for that you'd want to get more familiar with Londiste's various commands. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] how to best resync serial columns

the last_value column in each seq table to the max(id) from the relevant table. You shouldn't edit sequence table directly. To set a sequence's value you should use the setval(seqname, seqval) function like so: SELECT setval('some_seq', 1000); Erik Jones, Database Administrator Engine Yard

Re: [GENERAL] After delete trigger problem

. As far as how to get around it we'd need to know a little more about what the trigger is actually supposed to do. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list

Re: [GENERAL] After delete trigger problem

On Nov 7, 2008, at 11:24 AM, Erik Jones wrote: On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote: Hello, I have a child table with CONSTRAINT fkey FOREIGN KEY (x) REFERENCES master (x) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE. and CREATE TRIGGER td_y AFTER DELETE ON chlid

Re: [GENERAL] Drupal and PostgreSQL - performance issues?

if you're going to go the Python route. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] ]OT] Parsing postgresql.conf archive_command

/backup/wal/%f = / backup/wal) So, that's dirname on the results of grepping for the line that starts with archive_command piped through a basic awk (split on spaces) printing the last filed piped through an awk splitting on a single quote printing the first field. Erik Jones, Database

Re: [GENERAL] Backend timeout

to client_addr and run (without the brackets): lsof -i tcp:client_port If there's still a client for that connection you should turn up a process there. If that's the case then you should be tracking down why your client connection are holding on to open transactions. Erik Jones, Database

Re: [GENERAL] connection timeouts and killing users

the child connections) as config reload will take care of pg_hba.conf changes. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability (415) 963-4410 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Stuck query

an ssh tunnel and was sitting in FIN_WAIT2 status. Killing the client process individually made everything go away nicely without any kind of extra downtime necessary. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability (415) 963-4410 x 260 Location: US/Pacific IRC

Re: [GENERAL] Conflict between MVCC and manual locking

On Sep 2, 2008, at 4:16 AM, jose lawrence wrote: HI, I want to get more information whether MVCC conflicts with manual locking ? Have you read the chapter on MVCC and locks? http://www.postgresql.org/docs/8.2/interactive/mvcc.html Erik Jones, Database Administrator Engine Yard Support

Re: [GENERAL] PITR problem

starting up the standby? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Deadlock situation?

running against it (alter tables, index builds/drops, etc...)? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing

Re: [GENERAL] inheritance. more.

. You have duplicates in slave, not master, and there is not unique constraint on slave. They are physically separate tables and Postgres doesn't yet handle inheritance of constraints from parent to child tables via inheritance. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401

Re: [GENERAL] PITR problem

WALs and possibly have that a little too aggressive. Do you have the -k flag set in your pg_standby call in your restore_command? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us

Re: [GENERAL] inheritance. more.

On Apr 28, 2008, at 8:01 PM, Gurjeet Singh wrote: On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones [EMAIL PROTECTED] wrote: Postgres doesn't yet handle inheritance of constraints from parent to child tables via inheritance. Was it done by design or was it a limitation we couldn't get over

Re: [GENERAL] Best backup setup

or so. Your suggestions are much appreciated! Are you repeating this question for which you previously received answers? Or, is the list server repeating messages? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere

  1   2   3   4   5   >