[GENERAL] datomic db as foreign data wrapper?

2014-03-15 Thread Reece Hart
Does anyone have experience accessing a datomic database using a foreign data wrapper? I'd be quite content with read-only and discarding the transaction data. The real goal is to explore data in datomic using SQL. Thanks, Reece

Re: [GENERAL] automatically refresh all materialized views?

2014-02-18 Thread Reece Hart
On Fri, Feb 14, 2014 at 10:15 AM, Merlin Moncure mmonc...@gmail.com wrote: yeah -- you could do this with some gymnastics and some dynamic SQL. If I were lazy (check), I would just encode the order in the name of the view somehow. Thanks. That's exactly what I do already. Apparently, I'm

[GENERAL] plans for plpython in RDS?

2014-02-18 Thread Reece Hart
Does anyone know if there are plans to support plpython in Amazon's RDS? I (approximately) understand the issue, but I don't know if there's any effort to remedy the problem or, rather, I shouldn't bother hoping. Thanks, Reece

[GENERAL] automatically refresh all materialized views?

2014-02-14 Thread Reece Hart
Is there a way to automatically refresh all materialized views, preferably in order of dependendency? I should be able to chase pg_depends entries to create this ordering, right? Thanks, Reece

Re: [GENERAL] password-less access, without using pg_hba

2014-02-08 Thread Reece Hart
On Fri, Feb 7, 2014 at 8:27 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: Ignoring the scary security issues One of the niceties of an RDS deployment is that I don't care much about the security issues: The machine is not in our VPC, there's only public data on it, and I

[GENERAL] password-less access, without using pg_hba

2014-02-06 Thread Reece Hart
I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to achieve password-less login in postgresql? I tried

Re: [GENERAL] password-less access, without using pg_hba

2014-02-06 Thread Reece Hart
On Thu, Feb 6, 2014 at 6:37 PM, David Johnston pol...@yahoo.com wrote: Doubtful. Yeah, that's what I had assumed too. The question is motivated entirely by what I think would make it easier for users. In principle it's not difficult to give people a password (as I do now), but in practice

Re: [GENERAL] equivalent of mysql's SET type?

2011-03-10 Thread Reece Hart
On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure mmonc...@gmail.com wrote: create type validation_flags as ( cluster bool, freq bool ); Wow. That solution is nearly sexy, and far and away better than any solution that I would have come up with. Thanks, Merlin! -Reece

Re: [GENERAL] equivalent of mysql's SET type?

2011-03-09 Thread Reece Hart
On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce pie...@hogranch.com wrote: why not just have a set of booleans in the table for these individual on/off attributes? wouldn't that be simplest? I like that approach, but I think it's unlikely to fly in this specific case for a couple reasons.

[GENERAL] equivalent of mysql's SET type?

2011-03-08 Thread Reece Hart
I'm considering porting a MySQL database to PostgreSQL. That database uses MySQL's SET type. Does anyone have advice about representing this type in PostgreSQL? MySQL DDL excerpt: CREATE TABLE `transcript_variation` ( `transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

Re: [GENERAL] equivalent of mysql's SET type?

2011-03-08 Thread Reece Hart
On Tue, Mar 8, 2011 at 5:52 PM, Darren Duncan dar...@darrenduncan.netwrote: Try starting with an enum type to define the possible values: CREATE TYPE Consequence_Type AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED', 'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE'); ... and then you could

Re: [GENERAL] equivalent of mysql's SET type?

2011-03-08 Thread Reece Hart
On Tue, Mar 8, 2011 at 5:51 PM, Steve Atkins st...@blighty.com wrote: tsvector or intarray might be other options, depending on how you're going to query it and whether those are really strings or more enum-ish. The more SQLy way of doing it would be to have that stored in a separate table,

Re: [GENERAL] pg_dump issues

2010-02-02 Thread Reece Hart
On 02/02/2010 03:41 PM, DM wrote: You were right, after zipping the dump file it came out to 6.9G Also check out the custom pg_dump format (pg_dump -Fc ...), which is compressed. There are caveats regarding portability across PG versions with the custom format, which you may want to

Re: [GENERAL] Deleting 100 rows which meets certain criteria

2009-12-30 Thread Reece Hart
On Wed, 2009-12-30 at 09:11 -0800, shulkae wrote: I want to delete all rows exceeding 400 hours (10 days) and I want to limit deletion of only 100 records at a time. If your table has a primary key or you can contrive one, then the DELETE FROM tbl WHERE pk in (SELECT pk FROM tbl WHERE

Re: [GENERAL] Q: data modeling with inheritance

2009-07-03 Thread Reece Hart
On Thu, 2009-07-02 at 19:19 -0700, Nathan Boley wrote: Is an association, for example, an experiment that establishes a dependent relationship? So could there be multiple associations between variant and phenotype? Exactly. You might have one group say that allele X causes some trait, whereas

Re: [GENERAL] Q: data modeling with inheritance

2009-07-03 Thread Reece Hart
On Fri, 2009-07-03 at 11:29 -0700, David Fetter wrote: I'm missing what you're doing here that foreign keys don't cover. Could you send along your DDL? No DDL yet... I'm just in the thinking stages. FKs technically would do it, but would become unwieldy. The intention was to have subclasses of

[GENERAL] Q: data modeling with inheritance

2009-07-02 Thread Reece Hart
This is a question about data modeling with inheritance and a way to circumvent the limitation that primary keys are not inherited. I'm considering a project to model genomic variants and their associated phenotypes. (Phenotype is a description of the observable trait, such as disease or hair

Re: [GENERAL] Providing an alternative result when there is no result

2009-05-18 Thread Reece Hart
On Mon, 2009-05-18 at 15:13 -0400, Joshua Berry wrote: Is there an easy and efficient way to return a boolean false for a query that returns no result, and true for one that does return a result? Presuming that you're not using the values in temp_table, I think you should be using PERFORM *

[GENERAL] text column indexing in UTF-8 database

2009-03-12 Thread Reece Hart
USING btree (alias) -[ RECORD 4 ]- indexname | pannotation_alias_cf indexdef | CREATE INDEX pannotation_alias_cf ON pannotation USING btree (lower(alias)) -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general

Re: [GENERAL] text column indexing in UTF-8 database

2009-03-12 Thread Reece Hart
using a_t on a (cost=0.00..8.27 rows=1 width=4) (actual time=0.061..0.062 rows=1 loops=1) Index Cond: (t = 'foo'::text) Total runtime: 0.099 ms (3 rows) script at http://harts.net/reece/tpo-test.sql -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general

Re: [GENERAL] text column indexing in UTF-8 database

2009-03-12 Thread Reece Hart
!= \s). And, I guess that alias ~* regexp requires a seqscan because the index isn't ordered over ~* (right?). How about lower(alias) ~* regexp ? Is PG smart enough to know that that ordering is well defined? Is my head on straight there? Thanks again, Reece -- Reece Hart, http://harts.net/reece

Re: [GENERAL] Pet Peeves?

2009-01-31 Thread Reece Hart
My two: * lack of PK/unique indexes on inherited tables (workarounds possible but annoying) * lack of auto-tuning or tuning tools (or perhaps my lack of awareness of them?) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] Unison Protein Database manuscript

2009-01-02 Thread Reece Hart
site I'm open to all feedback and problem reports. Cheers, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- 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] Stroring html form settings

2008-09-26 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] PostgreSQL process architecture question.

2008-09-10 Thread Reece Hart
contention for memory (for parallel sorts, say) becomes critical. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] partitioned table insert triggers

2008-06-22 Thread Reece Hart
INTO ' || ctable || ' VALUES( ($1).* )' USING new; Domo arigato, Itagaki-san. (I think I got that right.) The 8.3 method works like a charm. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] partitioned table insert triggers

2008-06-21 Thread Reece Hart
is: how? (Okay, I actually do have one more idea: construct the insert values list with quote_literal. I'm hoping there's a better way.) Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Annoying messages when copy sql code to psql terminal

2008-06-06 Thread Reece Hart
the same files (i.e., are running on the same machine or you're saving to a network fs), it's more reliable to save the file and read it in psql with \i or with psql -f. (\i and -f won't try to do tab completion, of course.) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Copying data from a table to another database

2008-05-25 Thread Reece Hart
using the COPY method above). This won't work if there are intentional identical rows in your table. The pipe assumes a Unix-ish box. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Short-circuiting FK check for a newly-added field

2008-05-22 Thread Reece Hart
On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote: I need to add a field to a fairly large table. In the same alter statement I'd like to add a FK constraint on that new field. Is there any way to avoid the check of the table that the database is doing right now? The check is

Re: [GENERAL] how to modify a view

2008-05-20 Thread Reece Hart
_someview_v_20080520.) When you rename a table or view, the definition of any views which depend on them will be updated appropriately. This is particularly handy when you have several layers of views and don't want to break current behavior. -Reece -- Reece Hart, http://harts.net/reece/, GPG

Re: [GENERAL] Link tables from different databases

2008-05-18 Thread Reece Hart
drink, and a long-hard think about the prudence of such a design. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- 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] Unloading a table consistently

2008-05-13 Thread Reece Hart
about whether a table partition might be better here? Could an insert rule on a table partition automatically create the inherited table, if needed, and insert there? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Scripting function definitions as SQL?

2008-05-11 Thread Reece Hart
-fx.toc mydb.pgdfc The output of pg_restore is sql. This technique is extremely useful for other kinds of schema elements as well. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] tsearch2 and hyphenated terms

2008-04-11 Thread Reece Hart
parser? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- 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] tsearch2 and hyphenated terms

2008-04-11 Thread Reece Hart
with tsearch2. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- 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] Survey: renaming/removing script binaries (createdb, createuser...)

2008-03-29 Thread Reece Hart
. consistent with 1 4. c Blue. No, red. Aaah... -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Postgresql partitioning

2008-03-22 Thread Reece Hart
criterion changes into the database. I've not done this myself, so I'm, um, not speaking from experience. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] postgre vs MySQL

2008-03-12 Thread Reece Hart
for all engines or whether it might have been fixed. Having to shutdown a database to make a backup is a non-starter for anything that other than a toy (or read-only) databases. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] executing query results withing psql

2008-03-05 Thread Reece Hart
+ \i option most for consistency and robustness (don't have to specify the db connection or worry about connecting to the wrong db), but what I really want is a hypothetical \eval to execute the query and then execute the results. Any such thing? Thanks, Reece -- Reece Hart, http://harts.net

Re: [GENERAL] dynamic crosstab

2008-02-13 Thread Reece Hart
in the database but rather by psql. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 6: explain analyze is your friend

[GENERAL] Good F/OSS license summary

2008-02-01 Thread Reece Hart
, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Oracle Analytical Functions

2008-01-30 Thread Reece Hart
12:03:00 | 00:05:00 (4 rows) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 create table data ( client_id integer, datetime timestamp not null ); create index data_client_id on data(client_id); copy data from STDIN DELIMITER ','; 122,2007-05-01 12:00:00 122,2007-05-01 12

Re: [GENERAL] OT - pg perl DBI question

2008-01-29 Thread Reece Hart
that is. -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Deadlock in Postgres 8.2

2008-01-21 Thread Reece Hart
| pg_class_oid_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00 15716 | csb-dev | pg_catalog | pg_class_relname_nsp_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00 ... -- Reece Hart, http

Re: [GENERAL] (un)grouping question

2008-01-21 Thread Reece Hart
is a PostgreSQL-ism. See http://www.postgresql.org/docs/8.2/static/queries-select-lists.html#QUERIES-DISTINCT . -Reece, your heterographic brother -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] (un)grouping question

2008-01-21 Thread Reece Hart
is unique then something like = select uid,somevalue,somevalue||'-'||uid from mytable is much better. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] double free corruption?

2007-12-31 Thread Reece Hart
/log/messages (assuming you're logging locally). Consider looking there to see if you have other instances of this bug with other applications. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] comparing rows

2007-12-10 Thread Reece Hart
of adjacent rows with large deltas. The easiest way to get the associated data is to join on the original data table where ts1=ts or ts2=ts. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] any way for ORDER BY x to imply NULLS FIRST in 8.3?

2007-11-06 Thread Reece Hart
. NULL) of foo is arbitrary. And, I guess that the equivalence of this query on two systems will depend on the collating locale also (but I'm a neophyte in this area). -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast

Re: [GENERAL] getting list of tables from command line

2007-10-31 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] Multiple versions on same box

2007-10-08 Thread Reece Hart
* variables. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint master_pkey is raised? - Is this a Bug?

2007-09-24 Thread Reece Hart
; update master set m1=-m1+1; commit; You could just as easily add N to m1, then subtract (N-1) from m1. You'll need Nmax(m1). Good luck, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Why the ERROR: duplicate key violates uniqueconstraint master_pkey is raised? - Is this a Bug?

2007-09-24 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Seq Scan

2007-06-01 Thread Reece Hart
the index overhead. These decisions are not always correct, but they're usually quite good. If you don't believe it, try: # set enable_seqscan=off; # explain analyze ... # set enable_seqscan=on; # explain analyze ... -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Make for PgSQL?

2007-05-31 Thread Reece Hart
, such as -- requires: utils.sql common.sql create or replace function ... Then you'd automatically generate a file of sql dependencies using a perl one-liner (or whatever). -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast

Re: [GENERAL] Command line export or copy utility?

2007-05-22 Thread Reece Hart
with pg_restore -l, edit the TOC to include only the entries I want, and then rerun pg_restore with -L. Good luck, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner

Re: [GENERAL] Random Sample

2007-05-18 Thread Reece Hart
(if your table has = 100 rows, of course). -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Vacuum non-clustered tables only

2007-05-17 Thread Reece Hart
the script, and another to execute it. Good luck, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL

Re: [GENERAL] Views- Advantages and Disadvantages

2007-05-09 Thread Reece Hart
width=0) Index Cond: (tax_id = 9606) (9 rows) Long ago I compared a few views with their inlined counterparts and the upshot is that there is exactly or practically zero difference. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] IF function?

2007-05-01 Thread Reece Hart
? --- It's true (1 row) The function is NOT declared strict: it can return null on null input. I think that is the right behavior for the boolean, but not for the return strings. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end

Re: [GENERAL] Schema as versioning strategy

2007-04-25 Thread Reece Hart
. -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[GENERAL] digest data types?

2007-04-12 Thread Reece Hart
that the space saved by storing digests as binary (1/2 size of hex) will substantially impact index ins/upd/del performance or when the digest itself is a large fraction of the rest of the row size? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] digest data types?

2007-04-11 Thread Reece Hart
that the space saved by storing digests as binary (1/2 size of hex) will substantially impact index ins/upd/del performance or when the digest itself is a large fraction of the rest of the row size? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] storing checksums in a database

2007-04-10 Thread Reece Hart
that provide these conversions? If not, I'll do it myself... but I'd rather steal of course. Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe

Re: [GENERAL] Dealing with table names in functions

2007-03-22 Thread Reece Hart
'::regclass::oid::FQregclass; regclass -- unison.pseq (1 row) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources

Re: [GENERAL] deleting a foreign key that has no references

2007-03-19 Thread Reece Hart
within the trigger itself. Otherwise, you might have to store/update these queries in a separate table after every DDL change. See the pg_depends documentation at http://www.postgresql.org/docs/8.2/interactive/catalog-pg-depend.html -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] deleting a foreign key that has no references

2007-03-19 Thread Reece Hart
On Mon, 2007-03-19 at 13:03 -0700, Reece Hart wrote: The harder and more general case is to build such a query dynamically from pg_depends ... See the pg_depends documentation at Apologies. I intended to write pg_constraint and the documentation at http://www.postgresql.org/docs/8.2

Re: [GENERAL] Postgres Database size

2007-03-17 Thread Reece Hart
| 3905 kB csb | 113 GB template0 | 3840 kB csb-dev | 124 GB csb-dev-snapshot-2007-03-08 | 123 GB csb_02_02_2007 | 121 GB template1 | 3840 kB -- Reece Hart, http://harts.net/reece/, GPG

Re: [GENERAL] [SQL] PostgreSQL to Oracle

2007-03-16 Thread Reece Hart
there enlighten me? What's the intent for HSODBC and what are its limitations? Can one access remote server side functions via HSODBC (eg, sqlplus select col1,plpgsqlfx(col1) from remotetable)? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19

[GENERAL] Oracle-PostgreSQL Coexistence/Interoperability (segue from PostgreSQL to Oracle)

2007-03-16 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.

[GENERAL] orphaned PGDATA/base/ subdirectories

2007-03-13 Thread Reece Hart
guess is that these orphaned directories result from those interruptions and failure. Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing

Re: [GENERAL] orphaned PGDATA/base/ subdirectories

2007-03-13 Thread Reece Hart
and was about to restart the cluster anyway, for which I'd expect remnants. However, I'm certain of the observation that disk full led to remnants of a partial copy. That job was running in the background when I wasn't logged in. Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe

Re: [GENERAL] orphaned PGDATA/base/ subdirectories

2007-03-13 Thread Reece Hart
, and I can respond to 15 emails while coding on 3 branches and running regression tests... this is great! -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources

Re: [GENERAL] Database deadlock/hanging

2007-03-07 Thread Reece Hart
| IDLE| 00:00:21 28434 | csb-dev | pgutils| locks | relation | AccessShareLock | RUN | rkh | IDLE| 00:00:21 (6 rows) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel

[GENERAL] pg_restore and database comments

2007-03-07 Thread Reece Hart
a database? createdb -T db db-snapshot seemed like a good idea, but that requires the template db to not be in use (ie, no logged in users). I'm running 8.1.8 and will move to 8.2.3 shortly. Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19

Re: [GENERAL] Union Query Improvement

2007-02-14 Thread Reece Hart
You should consider inherited tables and table partitioning. See http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html Partitioning offers two administrative benefits: consistency of child tables and an implied UNION ALL of children when one selects on the parent. -Reece -- Reece

Re: [GENERAL] Foreign Key Identification

2007-01-10 Thread Reece Hart
and this should list me I needed to do something similar. I wrote some views to do this and packaged 'em up as something I call pgutils. See: http://archives.postgresql.org/pgsql-general/2006-11/msg00752.php http://harts.net/reece/pgutils/ -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] select union with table name

2006-12-28 Thread Reece Hart
' as table_name from blue union all select name,'red' as table_name from red; Note the 'all' after union... I suspect you'll want that or should at least consider it. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush

Re: [GENERAL] (bash the search engine) Don't split on underscore

2006-12-19 Thread Reece Hart
. Same with most other functions. That would be useful and almost certainly result in better specificity. A counter example is searching for information schema, for which you'd probably want hits to information_schema as well. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Let's play bash the search engine

2006-12-18 Thread Reece Hart
. Is that a stemmer problem? -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.

Re: [GENERAL] Anyone? Best way to authenticate postgres against

2006-12-18 Thread Reece Hart
http://www.windowsnetworking.com/articles_tutorials/Authenticating-Linux-Active-Directory.html If you can make that work, the community might appreciate a summary of how you did it. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass

Re: [GENERAL] MySQL drops support for most distributions

2006-12-13 Thread Reece Hart
concerns. The only reason I spent this much time weighing in is because I'm thrilled with PostgreSQL (er, sorry Tom, Postgres) and appreciate and respect the terrific work done in this community. Thank you. Cheers, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11

Re: [GENERAL] Copy command to load data into a PostgreSQL DB

2006-11-17 Thread Reece Hart
into the single quotes rather than \x05 or any other representation of it (e.g., \x05). I'm pretty sure that readline is responsible for this interaction, and therefore this probably doesn't work on readline-less installations. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] database name aliases?

2006-11-07 Thread Reece Hart
Woody and Albe- I wasn't aware of pg_service -- that does solve my original problem. Thanks for the replies. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] R and postgres

2006-11-07 Thread Reece Hart
almost sent you an off-list email before I posted because I just knew you'd know the answer. Thank you. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] database name aliases?

2006-11-06 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] database name aliases?

2006-11-06 Thread Reece Hart
itself to ensure consistency. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] R and postgres

2006-11-06 Thread Reece Hart
Thing for a guy who wants R to talk to postgresql? Thanks, and send aspirin, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] R and postgres

2006-11-06 Thread Reece Hart
, histogram, etc). I appreciate that I could expose this through postgres, but that's a little awkward for prototyping and I'm not keen to add load to my postgres box. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] database name aliases?

2006-11-06 Thread Reece Hart
new connections. Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] DELETE performance issues

2006-11-02 Thread Reece Hart
know what's appropriate for current multicolumn index behavior. The code is in http://harts.net/reece/pgutils/ . -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] not null across multiple columns

2006-11-02 Thread Reece Hart
, constraint valid_name_or_email check (name is not null or email is not null) ); Cheers, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 3: Have you checked our extensive

Re: [GENERAL] creating a dumpfile from a view

2006-10-30 Thread Reece Hart
be obtained by synthesizing a create table statement appropriate for the psql data export as above and using the copy command to load data. That could all be wrapped into a single file to be passed to psql for loading. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] Deleting Problem

2006-10-30 Thread Reece Hart
that the constraints are consistent at the end of the transaction, perhaps the problem is merely the order in which constraints are checked. See http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html and consider something like SET CONSTRAINTS ALL DEFERRED . -Reece -- Reece

Re: [GENERAL] Postgresql 6.13

2006-10-14 Thread Reece Hart
with two 13 cent stamps as soon as I finish watching War Games on my betamax. Is Carter still president? -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] using schema's for data separation

2006-09-29 Thread Reece Hart
policy (over having the policy in functions). On the other hand, I could be blowing smoke -- it seems like this outta work, but I haven't actually done this myself. I don't actually do any real work myself. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] text formatting in a query, a la sprintf

2006-08-19 Thread Reece Hart
within in a single string... ick! Is there a way to get more flexible formatting as in these examples within a backend query, even if not with sprintf per se? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

[GENERAL] text datum VARDATA and strings

2006-08-14 Thread Reece Hart
);/* w/null */ if (!out) { elog( ERROR, couldn't palloc (%d bytes), n+1 ); } for( i=0, oi=out; i=n-1; i++ ) { char c = toupper(in[i]); if ( isseq(c) ) { *oi++ = c; } } *oi = '\0'; return(out); } -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: [GENERAL] text datum VARDATA and strings

2006-08-14 Thread Reece Hart
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

  1   2   >