[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 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 could not get t

[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 log_tem

Re: [GENERAL] Age of the WAL?

On Mar 12, 2013, at 4:13 PM, Tom Lane wrote: > Erik Jones 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 availa

[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 c

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

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

'm unsure what to do next 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 chan

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 queryin

[GENERAL] MySQL -> Postgres migration tools?

r mysql 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@postgres

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

source install and build it against your built source tree or use your package manager of choice to get it. Regardless: > >> ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2>/dev/null You are doing that *after* you run pg_start_backup() so you're deleting WAL fil

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 fro

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

_host} rm ${slavedb}/postmaster.pid 2>/dev/null > ssh ${slave_dbus...@${slave_host} rm ${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 arc

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

is what I want to avoid. 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 mail

Re: [GENERAL] Innotop for postgresl

e/monitoring-stats.html#MONITORING-STATS-VIEWS 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] Why grantor is owner in this case?

ar grant, there isn't any built-in way. What 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, Reliab

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

re company releases any kind of proprietary software 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 --

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

id AS > $BODY$ > DECLARE > mytxt 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, yo

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

or writes starving out the rest of their app thanks 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

[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 pgs

Re: [GENERAL] XLOG's implementation details

and am curious about how 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 p

Re: [GENERAL] XLOG's implementation details

and am curious about how 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 p

Re: [GENERAL] Catalog help

without an actual foreign key 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

Re: [GENERAL] Inheritance on foreign key

traints on a parent table 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

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

. 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] generic modelling of data models; enforcing constraints dynamically...

-- including checks 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

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

guide application db access APIs, discouraging ad-hoc SQL in the app. It's also another way to force 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

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

he data 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.pos

Re: [GENERAL] Idle in transaction help

that port open directly, no need 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 (pgsq

Re: [GENERAL] PostgreSQL and Poker

"stay away from 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.pos

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

If not, determine what host the ip actually does refer to. Then on whatever host the idle transaction is coming from run: lsof -itcp: where 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

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, ye

Re: [GENERAL]

id 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,

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

inserts always use the default 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 any

Re: [GENERAL] running pg_dump from python

/ancillary/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

Re: [GENERAL] Dynamic table

id | a +--- 2 | {2,5,NULL,NULL,10} 3 | {3,5,NULL,NULL,10} 1 | {NULL,5,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

Re: [GENERAL] warm standby with WAL shipping

has all 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 sub

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

you know, you can execute SET ROLE ; 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 vi

Re: [GENERAL] Schema per user?

ound really interesting. I could see using pl/ proxy to handle 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 Admin

Re: [GENERAL] Schema per user?

I'll let you 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, Da

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 the

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 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] date ranges in where

OR WHERE lastlogin >= 'X' 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

Re: [GENERAL] date ranges in where

is inclusive of the two values given so 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@postgres

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

ou can do this: WHERE lastlogin >= 'X' 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 mail

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

table 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 ch

Re: [GENERAL] triggers and execute...

ty simple. Scott, I also couldn't come up 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 J

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

4 httpd 9 init 25 lsof 39 memcached 807 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

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

y much on with regards 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 Yar

Re: [GENERAL] Can Autovaccuum also reindex

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

ion (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

those lines. On Apr 14, 2009, at 5: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, Er

Re: [GENERAL] PITR - warm standby switchover question

ry? You should use pg_standby's -t flag to specify a stop file. Then all you 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, Dat

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

te is stored in pg under the session id. ken *coming up to speed slowly, going to look up search_path* If you're using pg_dump for backups then you'll probably want at least a standard prefix on your "temp" schemas so that you can easily have pg_dump ignore them when doin

Re: [GENERAL] high load on server

0 0 0 332 2319 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/mailpref/pgsql-general

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

most *nix users and would 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/Pa

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

Re: [GENERAL] [SQL] bash & postgres

On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: Erik Jones 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) script, I&#x

Re: [GENERAL] [SQL] bash & postgres

) in a bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: #!/bin/bash #!/bin/bash two="2" psql -d pagila <(P.S. Your quotes around $two in your original are not needed, in fact they're straight up

Re: [GENERAL] pg_restore error - Any Idea?

-Fc 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

[GENERAL] Last modification time of a database?

the given operation 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 Loca

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

reason 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 Admin

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

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 wrote: From: Erik Jones Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...

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

antly 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 (#

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

would definitely prefer to get the time elapsed between the start of the java program and the end of the copy command... Is that possible ? If not at least the start and the end time so tht i can do the calculation myself Tom just showed you how. Use run full piped command with the unix '

Re: [GENERAL] 8.3 doc issue

http://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:

Re: [GENERAL] Pet Peeves?

removed 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, Scalabili

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 is not a

Re: [GENERAL] Warm Standby question

napshot of the data directory. In addition, the copy 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, Rel

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 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 in FireBird,

Re: [GENERAL] PGSQL or other DB?

s no "yes" or "no" answer or "here's the right way to do it" and "here's the wrong way". You need to define your requirements and part of that is asking questions. For example, 'how to configure..' -- if you seriously ask a question

Re: [GENERAL] Pet Peeves?

s the ability 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

Re: [GENERAL] Inheritance question

ering such that when processed in a certain order, 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, Reliabilit

Re: [GENERAL] Description of transaction model for indexes

n 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:

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 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 the new *col

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 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 dom

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

, by "fixing" the behavior to be what you're expecting you'd 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 m

Re: [GENERAL] Mirroring existing mysql setup

You can use CREATE INDEX CONCURRENTLY to avoid the table locks. However, 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

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

LE/ALTER TABLE statements, they're still independent objects that can be ALTERed directly but a subsequent ALTER on the owning table propagates to the sequence. That being said, I'm satisfied now that I know that it isn't something that's already supposed to work.

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 d

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

to postgres; ALTER LANGUAGE foo=# drop user foouser; ERROR: role "foouser" 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, Re

Re: [GENERAL] No serial type

it to find the max value already in the column and automatically set it's start value based 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 -- Sen

Re: [GENERAL] Upgrading side by side in Gentoo

er said that Gentoo for for single users 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 J

Re: [GENERAL] how to best resync serial columns

pdate 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

Re: [GENERAL] Upgrading Postgres question

ste can be used for normal, constantly running 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: ma

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] 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

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:

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

; | awk -F\' '{print $1}'` dirname will directory component of a path (dirname /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 fi

Re: [GENERAL] Backend timeout

from pg_stat_activity 2. Go to client_addr and run (without the brackets): lsof -i tcp: 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 transactio

Re: [GENERAL] connection timeouts and "killing" users

to kill off 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@postgres

Re: [GENERAL] "Stuck" query

cess. In our case, the client process was a connection made via 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 Suppor

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 Y

Re: [GENERAL] Deadlock situation?

it? Do you have any ddl queries 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.my

Re: [GENERAL] PITR problem

breaks. So, the question is: why does does the server say that it only needs 0001000100DB and later? Did you clear out your pg_xlog directory before starting up the standby? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organ

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

Re: [GENERAL] PITR problem

hat is the full pg_standby command string (restore_command=) in your recovery.conf. It sound's like you have pg_standby set to delete archived 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? Eri

Re: [GENERAL] inheritance. more.

able, with duplicates... No, you don't. 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 Jo

Re: [GENERAL] query question really cant give a summary here so read the body ;-)

. Now, should we have more exchanges to determine who can use the most flowery of speech or should we talk pgsql and schema changes? Perhaps his db-fu has yet to bud? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere com

  1   2   3   4   5   >