[GENERAL] Dynamic WHERE clause to call DB-function

I was wondering if something like this could be possible: SELECT * FROM table1 WHERE a1 = ... AND a2 = ... AND b1 = ... AND b2 = ... The first thing to note is that the WHERE clause is dynamic, as in it can contain more parameters or lesser parameters. But, what I want to try accomplish

[GENERAL] Weird insert issue

#x27;d expected that each of the two would be single operations, but this error is making me rethink that. Any thoughts? Larry -- 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] Weird insert issue

the cases where it's not a concurrent update failure) and catch the failure to verify that the data exists - if it does, I'll ignore the failure; if not, i'll throw an exception. Larry On Sat, Jun 27, 2015 at 10:57 PM Pavel Stehule wrote: > 2015-06-28 6:52 GMT+02:00 Peter Geog

Re: [GENERAL] Postgresql 9.4 and ZFS?

? > HowDoesZFSonLinuxHandleAdvacedFormatDrives > > EG: 2^13 = 8192 > > > > No, that would be: zfs create -o blocksize=8192 /path/to/pgdata this is for the DATASET, not the POOL > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make ch

Re: [GENERAL] Corrupted Dabatabase

ve you run fsck on the filesystem? Is there any messages in /var/log/messages? -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

Re: [GENERAL] Uber migrated from Postgres to MySQL

FreeBSD? The current FreeBSD Ports collection ports only allow ONE version to be installed at a time. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281 -- Sent via

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

ing to the correct library can you think of any other issues with this? Data Directory naming, as well as keeping the init-scripts straight. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 17716 Limpia Crk, R

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

On 2016-07-29 15:06, Larry Rosenman wrote: On 2016-07-29 15:04, D'Arcy J.M. Cain wrote: On Fri, 29 Jul 2016 15:07:53 -0400 Bruce Momjian wrote: > The answer is either chroot or mount and run pg_upgrade on another > server. If you can afford the downtime you can also delete PG, &g

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

On 2016-07-29 15:14, Bruce Momjian wrote: On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote: >>I might take a look at the NetBSD package (I'm a developer) to see how >>hard it would be to allow multiple versions. We do keep all the lib >>stuff in a separate

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

On 2016-07-29 15:17, D'Arcy J.M. Cain wrote: On Fri, 29 Jul 2016 15:09:59 -0500 Larry Rosenman wrote: >> version to the bare version of the binary name. Example: >> - psql.8.3 >> - psql.9.1 >> - psql.9.3 >> - psql ==> psql.9.3 >> >> Other

Re: [GENERAL] FreeBSD x86 and x86_64

s py-postgresql rubygem-dm-postgres-adapter rubygem-do_postgres borg.lerctr.org /usr/ports/databases $ I'm running 9.5.1 on both 11-CURRENT, and 10.x -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 7011

Re: [GENERAL] Timestamp with timezone output

results with different timezones: > > -[ RECORD 6 ]---+--- > expiration_date | 2015-09-07 00:00:00+02 > -[ RECORD 7 ]---+--- > expiration_date | 2015-11-27 00:00:00+01 > > Shouldn't all value be converted to the same timezone ? > > Thank you for your help ! D

Re: [GENERAL] pros/cons of using "synchronous commit=off" - AWS in particular

On 06/20/2014 09:41 AM, Merlin Moncure wrote: On Thu, Jun 19, 2014 at 9:24 AM, Larry J Prikockis wrote: so from the much-loved https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server page, we have this: " PostgreSQL can only safely use a write cache if it has a battery backup. Se

[GENERAL] inequality testing in jsonb query

"tags": [ "enim", "aliquip", "qui" ] } Could I modify the following query to find those records where the date "registered" is between November 1, 2009 and November 30, 2009? SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; Thank you for your help. larry

[GENERAL] Is it possible to create an index without keeping the indexed data in a column?

your help. Larry

[GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

ow in the toast table. In total, the amount well under a GB when compressed outside of PG. Any guesses as to why there is so little compression of this data or how I might remedy the situation? Thanks much for your help. Larry

Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

wrote: > On 07/31/2014 01:44 PM, Larry White wrote: > >> Hi, >> >> I'm running an experiment on 9.4 beta 2. >> >> I put 275,000 identical JSON files into a table using JSONB (one per >> row). Each raw text file is 251K in size, so the total uncomp

Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

On Fri, Aug 1, 2014 at 2:20 AM, Jeff Janes wrote: > On Thursday, July 31, 2014, Larry White wrote: > >> Hi, >> >> I'm running an experiment on 9.4 beta 2. >> >> I put 275,000 identical JSON files into a table using JSONB (one per >> row). E

Re: [GENERAL] Re: Is it possible to create an index without keeping the indexed data in a column?

able *as if* the JSON were > > there, but not actually put the data in the table? I could either store > > the > > docs elsewhere and keep a reference, or compress them and put them in the > > table in compressed form as a blob. > > > > Thanks much for your help. &g

Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

sible JSON docs of 251K each. Column Type - Storage - TOAST table size JSONB - EXTERNAL - 2448 MB JSONB - EXTENDED - 2448 MB JSON - EXTENDED - 2504 MB TEXT - EXTERNAL - 2409 MB TEXT - EXTENDED - 40 MB On Fri, Aug 1, 2014 at 2:36

[GENERAL] jsonb creation functions?

There is a set of creation functions for json, such as: to_json(anyelement) There doesn't seem to be any equivalent functions for converting text to jsonb. Is there a way to do this? Thanks.

Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

ternal 66 MB With 111 iterations: Extended 69 MB External69 MB Hopefully they can fix this before the GA release. On Fri, Aug 1, 2014 at 12:38 PM, Jeff Janes wrote: > On Thu, Jul 31, 2014 at 11:36 PM, Larry White wrote: > >> >> On Fri, Aug 1, 2014 at 2:20 AM

Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)

Reported as bug #11109. On Fri, Aug 1, 2014 at 1:46 PM, Larry White wrote: > Jeff, > > Thank you for your help. This is a Postgres bug, but I don't think I'd > have figured it out without your help. > > What is happening is that if PG can, after compression, put

Re: [GENERAL] New wrapper library: QUINCE

I'm not a C++ developer, but this looks like a really well-designed API. Nice work. On Mon, Aug 18, 2014 at 9:21 PM, Jov wrote: > A cool project! > > Jov > blog: http:amutu.com/blog > > > 2014-08-19 6:43 GMT+08:00 Michael Shepanski : > > Hi PostgreSQLers, >> >> I've rele

[GENERAL] Anyone using Apache Helix to manage a PostgreSQL cluster?

Very interested in hearing of successful or unsuccessful attempts, issues, etc. Thanks very much. larry

[GENERAL] JsonB Gin Index is very large; is there a work around?

Hi, I'm using 9.4 beta 2. I ran a test using 4 of the largest Json docs from our production data set. The four files total to 59.4 MB of raw json, which is compressed by TOAST to 21 MB, which is great. The index, though, is 47 MB, bringing the total size of the data in PG to 68 MB. The index was

Re: [GENERAL] JsonB Gin Index is very large; is there a work around?

t; On Wed, Aug 20, 2014 at 1:53 PM, Larry White wrote: > > Is there anyway to index a subset of the data in a JSONB column? I'm > > thinking of something like declaring certain paths to be indexed? > > > Yes. See the expression index example in the jsonb documentation. > > -- > Regards, > Peter Geoghegan >

[GENERAL] indexed range queries on jsonb?

Hi, I'm trying to find a way to do a range query on json such that it will use an index. This seems possible given that jsonb supports btrees and expression indices. For example I have: create index t1 on document using btree ((payload->'intTest')); where: payload is a jsonb column and intTest

Re: [GENERAL] indexed range queries on jsonb?

Thank you Tom, I made the necessary changes and Explain now shows that the query will use the index. Thanks again for your help. On Tue, Aug 26, 2014 at 10:33 AM, Tom Lane wrote: > Larry White writes: > > Logically, what I want is to be able to make queries like this: > >

[GENERAL] LONG delete with LOTS of FK's

ot; CONSTRAINT "text_search_data_account_id_fkey" FOREIGN KEY (account_id) REFERENCES account(id) TABLE "troweprice2_values" CONSTRAINT "troweprice2_values_account_id_fkey" FOREIGN KEY (account_id) REFERENCES account(id) TABLE "usage" CONSTRAINT "usage_account_id_f

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-01 10:23, Shaun Thomas wrote: On 05/01/2013 10:17 AM, Larry Rosenman wrote: I have an app that we have a number of tables that all have FK relationships with the account table. OK so far. Referenced by: * Redacted one-billion foreign keys Wow. I really hope every single

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-01 10:17, Larry Rosenman wrote: I have an app that we have a number of tables that all have FK relationships with the account table. We did a massive (2900+ account, probably multi-thousand rows) delete from all the tables, and the delete from the account table is taking a lot of

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman writes: Question: Do all these need to have a bare index just on the account_id column, or is a multicolumn index with account_id first sufficient for the check to be reasonably quick? I would think that such an index would be sufficient

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman writes: Question: Do all these need to have a bare index just on the account_id column, or is a multicolumn index with account_id first sufficient for the check to be reasonably quick? I would think that such an index would be sufficient, but

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 15:50, Larry Rosenman wrote: On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman writes: Question: Do all these need to have a bare index just on the account_id column, or is a multicolumn index with account_id first sufficient for the check to be reasonably quick? I would think

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:20, Shaun Thomas wrote: On 05/09/2013 03:58 PM, Larry Rosenman wrote: "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" This is the statement it canceled on. I've found

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:22, Tom Lane wrote: Larry Rosenman writes: Ideas on how to debug? Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? regards, tom lane all the locks were clear, a

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:40, Tom Lane wrote: Larry Rosenman writes: On 2013-05-09 16:22, Tom Lane wrote: Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? all the locks were clear, and it was consuming CPU and do

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:43, Larry Rosenman wrote: On 2013-05-09 16:40, Tom Lane wrote: Larry Rosenman writes: On 2013-05-09 16:22, Tom Lane wrote: Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? all the locks

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-10 09:14, Tom Lane wrote: Larry Rosenman writes: Any ideas on how to figure out if we ARE getting seqscan check plans, and better fix it? Try an EXPLAIN ANALYZE VERBOSE on something that just deletes one row, and wait however long it takes. The printout should show how much time is

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-10 10:57, Tom Lane wrote: Larry Rosenman writes: On 2013-05-10 09:14, Tom Lane wrote: ... and verify you get a cheap plan for each referencing table. We don't :( Ugh. I bet the problem is that in some of these tables, there are lots and lots of duplicate account ids, such

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-16 17:52, David Kerr wrote: On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote: - On 2013-05-10 10:57, Tom Lane wrote: - >Larry Rosenman writes: - >On 2013-05-10 09:14, Tom Lane wrote: - >... and verify you get a cheap plan for each referencing table. - >

Re: [GENERAL] LONG delete with LOTS of FK's

likely, although you could try enable_seqscan=false as well. Dave The 9.2 upgrade DOES fix my issue. Thanks again, Tom and everyone. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 7868

Re: [GENERAL] LONG delete with LOTS of FK's

Gavin Flower wrote: >On 18/05/13 03:06, Larry Rosenman wrote: >> On 2013-05-16 18:35, David Kerr wrote: >> >>> - I'll take a look tomorrow, but we WERE seeing Seq Scan's against >>> - multi-million >>> - row tables, so I suspect Tom is righ

Re: [GENERAL] Using cp to back up a database?

If you want a consistent database (you *REALLY* do), pg_dump is the correct tool. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106 From: on behalf

Re: [GENERAL] Universal libpq.a ?

On Tue, 5 Feb 2008, Dave Page wrote: On Feb 5, 2008 3:07 PM, Larry Rosenman <[EMAIL PROTECTED]> wrote: On Tue, 5 Feb 2008, Dave Page wrote: Another option which may be doable for someone with more knowledge of make would be to build binaries for all architectures seperately (you can

Re: [GENERAL] Universal libpq.a ?

to choose an index scan if your joining column's datatypes do not match -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED] US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---

Re: [GENERAL] Universal libpq.a ?

nd just built it twice, and then lipo'd the 2 bin and lib directory files together as a quick hack. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index s

[GENERAL] 9.1.4: pg_restore: couldn't uncompress data?

of error [lrosenman@bpsandbox-db2 ~]$ ls -l db* -rw-rw-r-- 1 lrosenman blueprint 10838183690 Jul 23 17:58 db.backup.2012_07_21T062258 [lrosenman@bpsandbox-db2 ~]$ The file is the same size on both systems, and was transferred with scp. Any ideas why I would see this? -- Larry Rosenman

Re: [GENERAL] 9.1.4: pg_restore: couldn't uncompress data?

On Mon, July 30, 2012 9:14 pm, Craig Ringer wrote: > On 07/24/2012 09:50 PM, Larry Rosenman wrote: >> This one is concerning. Trying to restore a backup from one system to >> another, and got this: >> >> pg_restore: restoring data for table "userid" &g

[GENERAL] PostgreSQL 9 Mac OS X one-click install - PL/perl broken

l.so: mach-o, but wrong architecture even though the plperl.so I built looks ok: $ file plperl.so plperl.so: Mach-O bundle i386 Has anyone else run into this? Anybody have any suggestions? Thanks! Larry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] PostgreSQL 9 Mac OS X one-click install - PL/perl broken

... Excellent! Looks like that worked fine. I just added the "--with-perl" option to configure. Thanks Dave! Larry -- 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 Mac OS X one-click install - PL/perl broken

e.  I just added the "--with-perl" > > option to configure. > > > > Thanks Dave! > > You're welcome. I guess it is running the 64bit image - is your > machine Leopard Server? Not sure how I would check... sw_vers give me: ProductName:Mac OS X Produ

[GENERAL] Modeling tools

e the Toad Data Modeler. Looks like it may still be a good tool but I can't seem to get the sales staff to return my calls so I'm exploring alternatives, which is how I found MicroOLAP. Just wondering if anyone has tried to use this tool for schema modeling and DDL generation. Thanks. - larry

[GENERAL] PG_DUMP/PG_RESTORE duplicate Rows?

? Yes, we're moving to 8.4.1 on our next release (12-12-2009). I cleaned it up by: druckerdb=> delete from last_run where ctid='(0,10)' or ctid='(0,60)'; DELETE 2 and putting the constraint back in place. -- Larry Rosenman http://w

Re: [GENERAL] PG_DUMP/PG_RESTORE duplicate Rows?

Never mind. I had my appserver up, and it probably screwed up. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -Original Message- From: pgsql-general

[GENERAL] Transaction started test

igger. Cannot find any such function in the docs. Any help much appreciated. Best regards Larry Anderson

Re: [GENERAL] Transaction started test

Hi Craig, Many thanks for the detailed and quick reply. Must admit although I'd read that every statement was implicitly in a transaction I hadn't connected that through to the operations in any associated triggers. Best regards Larry Anderson Craig Ringer wrote: On 20/12/20

Re: [GENERAL] 64bits or 32 bits on ESX?

I have seen no difference in performance. Now, if you want large memory for a DB server, and you should, 64 is the way to go. I'm currently running CentOS 5 64-Bit vm's for the SaaS app I support. Works great on ESX 4U1. -- Larry Rosenman http://www.lerct

Re: [GENERAL] When is the release date for Postgres 8.5?

: - "Larry Rosenman" writes: On Thu, January 21, 2010 5:53 pm, Andreas Joseph Krogh wrote: Care to shed some light on what features (yes, we users care about features) warrant this major version-bump? Is there a link somewhere? AFAIR, it was stated if Hot Standby AND Streaming Repli

Re: [GENERAL] When is the release date for Postgres 8.5?

On Fri, 22 Jan 2010, John R Pierce wrote: Larry Rosenman wrote: Yeah. The question of "when do we call it 9.0" has come up multiple times over the past few release cycles, and "when we get built-in replication" has always been one of the more popular answers. If HS

Re: [GENERAL] PostgreSQL DBA in SPAAAAAAAACE

; >>>>> https://www.dbainspace.com/finalists/joe-miller >>>>> >>>>> Voting is open for 7 days. Don't let one of those Oracle or >>>>> SQL Server punks win :p >>>> so jealous -- I didn't make the cut. Well, you'll

[GENERAL] truncate capability in 8.1

g the pglargeobject table? - larry

Re: [GENERAL] Scalability with large numbers of tables

;> directories take a significant time. (In other applications I've >> generally used a guide of 100-1000 files per directory before adding >> extra layers, but I don't know how valid this is.) PostgreSQL breaks tables down into 1GB segments, and oversized attrib

[GENERAL] Is the FTP server down?

I get time out errors from all mirrors when trying to download 8.0 win binaries. Apologies if this is the wrong forum for this. thanks. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joinin

Re: [GENERAL] [SQL] How to add 1 hour in a date or time stamp?

On Monday 18 April 2005 08:38 am, Dinesh Pandey wrote: > How to add 1 hour in a date or time stamp? timestamp + '1 hour'::interval is one way. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail

Re: [GENERAL] Cannot compile on Slackware 10.2

Shot in the dark: Check your clock. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED

Re: [GENERAL] PostgreSQL on Dual Processors, Dual-Core AMD Chips

chips? Any information would be helpful... Thank you! Each connection is a separate process, so yes it will, assuming multiple connections to the backend. There are numerous threads in the archives on why we don't use threading. -- Larry Rosenman

Re: [GENERAL] insert a value into a table

double the backslashes.  They are an escape character.     --Larry Rosenman http://www.lerctr.org/~lerPhone: +1 972-414-9812 E-Mail: ler@lerctr.orgUS Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED

Re: [GENERAL] querying PostgreSQL version?

On Oct 26, 2005, at 7:52 AM, Zlatko Matić wrote: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, select version(); Zlatko -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152

Re: [GENERAL] postgres query log analysis?

> > Is there any other option? When you enable query logging in your > PGSQL logs what do you do with the output? > > Thanks! > Look into pgfouine on pgFoundry. http://pgfoundry.org/projects/pgfouine/ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1

[GENERAL] Nesting Stored Procedure Calls

Is it possible for one stored procedure to call another? I would like certain procs to call a different proc to update an audit trail whenever they're executed. I thought about using triggers but want the trail to include info that's not in the updated table - specifically the application user I

[GENERAL] Nesting Stored Procedure Calls

Is it possible for one stored procedure to call another? I would like a number of procs to call another proc to update an audit trail whenever they're called. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Newbie question: returning rowtypes from a plpgsql function

I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. Am I doing some

Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function

ld be helpful. Thanks. On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Larry White <[EMAIL PROTECTED]> writes: > > I wrote a function that returns a rowtype. The rowtype is assigned a > > value by a query using SELECT INTO. The query sometimes

[GENERAL] using inheritance in production application.

How 'ready for prime-time' is the table inheritance feature? I've seen some postings about particular issues (lack of full FK support, for example), but would like to get an overall sense of the stability and robustness of the feature. Also, is there a performance hit in using inheritance? For e

[GENERAL] transactions, functions, foreign keys

Hi, I've run into a situation (I should have forseen) and was hoping someone could show me a way out. I have a function that calls other functions. These other functions are inserting rows and return the primary key for the inserted row. Some of the tables are related in a way that they have

Re: [GENERAL] transactions, functions, foreign keys

:33:57 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Wed, Dec 15, 2004 at 11:53:55AM -0500, Larry White wrote: > > > I have a function that calls other functions. These other functions > > are inserting rows and return the primary key for the inserted row. > >

Re: [GENERAL] Creating an index-type for LIKE '%value%'

=# select * from blacklist limit 1; insert_when | insert_who | domain | message ++--+--- -- 2003-12-22 21:02:49-06 | ler| 008\.net | 127.0.0.1 MX, SPAMMER (008.net) (1 row) exim=# Thanks! LER -- La

Re: [GENERAL] Creating an index-type for LIKE '%value%'

he index with the 254 domains I have in my fecal roster, but it's also about 5x as fast as the other REGEX lookup. Thanks for the ideas! LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org U

Re: [GENERAL] Creating an index-type for LIKE '%value%'

On Tue, 8 Feb 2005, Oleg Bartunov wrote: On Mon, 7 Feb 2005, Larry Rosenman wrote: Oleg Bartunov wrote: Larry, I pointed you to pg_trgm module mostly following Martijn's suggestions. Now, I see you need another our module - ltree, see http://www.sai.msu.su/~megera/postgres/gist/ltree/ for de

Re: [GENERAL] Creating an index-type for LIKE '%value%'

Oleg Bartunov wrote: > On Tue, 8 Feb 2005, Larry Rosenman wrote: > >> On Tue, 8 Feb 2005, Oleg Bartunov wrote: >> >>> On Mon, 7 Feb 2005, Larry Rosenman wrote: >>> >>>> Oleg Bartunov wrote: >>> >>> Larry, I pointed you to pg_

Re: [GENERAL] Creating an index-type for LIKE '%value%'

Oleg Bartunov wrote: > On Tue, 8 Feb 2005, Larry Rosenman wrote: > >> >> It doesn't seem to like pieces with hyphens ('-') in the name, when I >> try To update blacklist set new_domain_lt=text2ltree(domain) I get a >> Syntax error (apparently for th

Re: [GENERAL] Creating an index-type for LIKE '%value%'

Oleg Bartunov wrote: > On Tue, 8 Feb 2005, Larry Rosenman wrote: > >> Oleg Bartunov wrote: >>> On Tue, 8 Feb 2005, Larry Rosenman wrote: >>> >>>> >>>> It doesn't seem to like pieces with hyphens ('-') in the name, when

Re: [GENERAL] Can't build libpq test example

testlibpq.o >> PQntuples testlibpq.o >> ld fatal: Symbol referencing errors. No output written to testlibpq >> >> ---(end of >> broadcast)--- >> TIP 2: yo

Re: [GENERAL] storing PDFs

former employer of mine. Works great. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3683 US ---(end of broadcast)--- T

Re: [GENERAL] Can't connect to postgresql using 'psql -d database -U user -W'

d you are not running the command as the unix user postgres. Check your pg_hba.conf file and the documentation on the pg_hba.conf file: http://www.postgresql.org/docs/current/static/client-authentication.html #AUTH-PG-HBA-CONF -- Larry Rosenman Database Support Engineer PERVASIVE

Re: [GENERAL] Granting Privleges on All Tables in One Comand?

on the TODO list: > > Also, you can do it today by making a plpgsql function that iterates > through all the tables and does GRANTs. There are examples in the > mailing list archives. (The TODO item would probably have been done > by now if it weren't so easy to work aroun

Re: [GENERAL] About Full-text searching under postgresql

rticle/21674/0/page/3 or http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Those are good places to start. using the contrib/tsearch2 module. LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531

Re: [GENERAL] Data type that corresponds to bigserial...

bytes > used to store the integer, or something like that. > > If I set the data type of the foreign key to "int8" in pgAdmin, will > this correspond to the integer type stored by the bigserial data > type? > > In other words, are int8 and bigint the same thing? >

Re: [GENERAL] SESSION_USER vs. CURRENT_USER

attribute of a function. CURRENT_USER is what is used for permission checks. see the manual. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: [EMAIL PROTECTED] Web: ww

[GENERAL] view creation question

I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a "CREATE VIEW myview AS" statem

[GENERAL] passing parameters to a trigger function

I can't figure out how to pass parameters to a trigger function.  I checked the documentation and saw that trigger functions don't take params in the usual fashion, but couldn't find an example of a pl-sql trigger function that used the original row data within the function. What I want is an on u

Re: [GENERAL] passing parameters to a trigger function (solved)

Thank you.  You guys rock!On 3/21/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: On Tue, 21 Mar 2006, Larry White wrote:> I can't figure out how to pass parameters to a trigger function. >> I checked the documentation and saw that trigger functions don't take> param

Re: [GENERAL] Autovacuum Logging

then look, > which will of course contain a whole lot of other chatter at that > level. > > The best solution I have seen so far is the idea of a GUC to control > autovacuums chatter level. I hope we get that becuase the above will > be a regression imho. I'

Re: [GENERAL] psql is slow and it does not take much resources

at could be the problem? Is that the server is just slow reading > from the file? It has a 300GB SCSI drive. > > Is there any way to make this work faster? I would like to recreate > the db at least once a month, but this taked too much time... > > Thanks in advance for any advi

Re: [GENERAL] psql is slow and it does not take much resources

tch the inserts between BEGIN;/COMMIT; pairs, or, better yet set it up as a COPY. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: [EMAIL PROTECTED] Web: ww

Re: [GENERAL] psql is slow and it does not take much resources

taking 3% of them. > I'll bet your WAL disk is mostly WAIT-I/O, waiting for the WAL log flushes at end of transaction. LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.

Re: [GENERAL] out of memory for query result

e prior month's data which had fewer results). This would either > point to an issue with a) amd64 memory allocation or b) palloc > interacting with 64bit memory or c) some other change. My suspicion is that it has to do with pointers being 8 bytes vs 4 bytes in amd64. -- Larry Rosenman

Re: [GENERAL] autovacuum "connections" are hidden

would it help people who are trying to determine if > pg_autovacuum is running? Would the connection still appear while > pg_autovacuum is sleeping? I believe while autovacuum is sleeping, the process is gone. I.e. it gets spawned anew for each pass looking for work. -- Larry Rosenman

  1   2   >