[GENERAL] WHERE (columnX IN (x,y,z)) ORDER BY columnY conflict

I've been trying to get this query to work to no avail. Both parts work in isolation. I am able to replace the IN clause with an ILIKE statement and the ORDER BY works fine. But in combination with the IN statement, the ORDER BY statement does not actually order the results, though no errors are th

[GENERAL] Help me with this tricky join

00101 19990101 How do I do this? A simple join wont do it since it is dependent on what value "date" is for each user.. Moreover, I know for a fact that the "date" in "selection" is the second largest value -> I want to add the 3rd largest value from "master" for each user. BR, Jay -- 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] Help me with this tricky join

CORRECTION: The original "selection" should be: User1 2101 NOT User1 19990101 -- 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] Help me with this tricky join

Thanks! But, since the master can contain many users (user2, user3, and so on) I suppose this won't be a proper solution? Sorry if I was a bit unclear in my description. I.e., the master is of the form: user_id date User1 20010101 User1 2101 User1 19990101 User1 19970101 User2 ... ... Btw, I

[GENERAL] pgsql on debian

Hi group, I am trying to get pgsql working on my debian system. I installed pgsql using apt-get. but then i cant connect to it . i am using the following command psql -U jay -d jaydb earlier i used create su postgres 'createdb root' jaydb. but now i cannot access the db. i get the fol

[GENERAL] postgresql on debian

hi people i am having trouble getting postgresql qork on my debian system. i used su postgres -c createdb jaydb to create a database.after the execution of command i got CREATE DATABASE COMMENT but when i try to acces the database i cannot access it . please help jay

Re: [GENERAL] pgsql on debian

hi oliver i tried the following command but nothing seems to work psql -U root -d root psql: FATAL: IDENT authentication failed for user "root" any suggestions thanks jay Oliver Elphick wrote: On Sun, 2004-06-27 at 00:36, Jay wrote: Hi group, I am trying to get pgsql working on

[GENERAL] postgresql perl connectivity

Proxy, Sponge, mysql. at psqldb.pl line 10 thanks jay ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

[GENERAL] trouble with query

;yes','no','yes','no','yes','yes','yes','yes','john',,'',,,'','',,'','',,'','',,''); i get the following error ERROR 1064: You have an

[GENERAL] Parallel query only when EXPLAIN ANALYZEd

s/AB97. So, why might postgres parallelize the query when I explain analyze it, but not when I just run it by itself? Thanks, Jay K

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

-> Index Only Scan using t2_item_low_high_idx on t2 (cost=0.42..63.77 rows=1333 width=12) (actual time=0.167..0.168 rows=12 loops=30) Index Cond: ((item = t1.item) AND (low <= t1.high) AND (high >= t1.low)) Heap Fetches: 0

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

t worked as expected (it launched workers with and without explain analyze), so I've only been able to reproduce this on Windows. Thanks, Jay K On Thu, Sep 29, 2016 at 9:07 PM David Rowley wrote: > On 30 September 2016 at 10:47, Jay Knight wrote: > >>What's max_worker_proc

[GENERAL] tx canceled on standby despite infinite max_standby_streaming_delay

I'm seeing long-running transactions (pg_dump) canceled on the standby when there are a lot of inserts happening on the master. This despite my having set max_standby_streaming_delay to -1 on the standby. Why might that happen? This is pg 9.3.12. When it happens I see: pg_dump: Dumping the con

Re: [GENERAL] tx canceled on standby despite infinite max_standby_streaming_delay

ng replication in the mean time. On Sat, May 14, 2016 at 8:20 PM, Venkata Balaji N wrote: > > On Sat, May 14, 2016 at 12:27 PM, Jay Howard > wrote: > >> I'm seeing long-running transactions (pg_dump) canceled on the standby >> when there are a lot of inserts happeni

Re: [GENERAL] tx canceled on standby despite infinite max_standby_streaming_delay

On Sun, May 15, 2016 at 6:15 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > Its customary to bottom-post (or respond inline) on these lists. > > On Sun, May 15, 2016 at 7:01 PM, Jay Howard > wrote: > >> Do you have hot_standby_feedback set to "o

[GENERAL] Restoring a database dump from 9.0 to 9.2

so far reading documentation I haven't really figured out if this is a viable path. If this is not a good way to do it I'm open for any and all suggestions. Thanks! Jay

[GENERAL] pg_dump question (exclude schemas)

I want to backup a database but exclude certain schemas with a patter. I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. Will this work? $pg_dump --exclude-schema='sch_*' this does not seem to exclude all schemas with this pattern ( 'sch_*' ), anything wrong here? than

[GENERAL] trigger or logging

Before running a script (invoked by .sh that will call stored procs which may invoke other triggers), I want to capture every change made in the database which includes the field value before the update and the field value after the update and also capture all inserts. With this data, I would be a

[GENERAL] subtracting from a date

I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. subtract o

Re: [GENERAL] subtracting from a date

for result data On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver wrote: > On 02/06/2014 09:25 AM, Jay Vee wrote: > >> I have reviewed working with dates documentation but some things are not >> clear and I cannot get an example to work for what I need. >> >> I am pa

[GENERAL] find_psql_error

I got tired of this: psql:lib/sql/similarity.sql:97: ERROR: column o.user_id does not exist at character 426 So I wrote this: https://github.com/jaylevitt/find_psql_error And you call it like this: find_psql_error "psql:lib/sql/similarity.sql:97: ERROR: column o.user_id does not exist at ch

[GENERAL] hashtable issue - HASH_FIND??

Hi there, I'm looking to use the PostgreSQL Hash Table for some custom functions. However, the example, as per http://wiki.postgresql.org/wiki/HashTable crashes postmaster (or segfaults from the cmd line) when trying to retrieve an element from the hash. Specifically this line fails: elem = ha

Re: [GENERAL] hashtable issue - HASH_FIND??

ation fault Wondering if it was just me..? - Original Message From: Tom Lane To: Jay Flattery Cc: pgsql-general@postgresql.org Sent: Wed, August 4, 2010 3:46:24 PM Subject: Re: [GENERAL] hashtable issue - HASH_FIND?? Jay Flattery writes: > I'm looking to use the PostgreS

Re: [GENERAL] hashtable issue - HASH_FIND??

ld_stub___error () #1 0x001abc54 in reaper () #2 #3 0x96c686fa in select$DARWIN_EXTSN () #4 0x001ad801 in ServerLoop () #5 0x001af407 in PostmasterMain () #6 0x0014c13b in main () Looks like something to do with linking. Needle in the haystack I think :( - Original Message From: To

[GENERAL] fork() and dynamically loaded c functions....

Hi there, I have some functions written in C that are dynamically loaded. I persist some state between function calls, initialised by _PG_init. This works fine I now fork() a process in _PG_Init() (both processes interact), but when I shutdown postgre I get: LOG: failed to find proc 0x1331110

Re: [GENERAL] fork() and dynamically loaded c functions....

Thanks for your reply >> I now fork() a process in _PG_Init() (both processes interact), but when I >> shutdown postgre I get: LOG: failed to find proc 0x1331110 in ProcArray >What exactly is that child process doing? It sure sounds like it thinks >it's a valid backend. Actually it's not doi

Re: [GENERAL] fork() and dynamically loaded c functions....

>You might find that doing on_exit_reset() in the child would fix the >worst problems, but it still sounds chancy as heck. Thanks for that. The fork() is certainly more convenient than exec'g something else - hopefully OK since we're just prototyping something. Quick question: where is _exit_re

[GENERAL] Learning to rephrase equivalent queries?

Sometimes the planner can't find the most efficient way to execute your query. Thanks to relational algebra, there may be other, logically equivalent queries that it DOES know how to optimize. But I don't know relational algebra. yet. (Date/Codd is a sleeping pill.) I need more experience fi

[GENERAL] PGError: ERROR: could not open relation with OID?

. Our volume is fairly low, the tables are fairly small, we have complete logs. How can I troubleshoot to see what might have happened? Or is this a known bug in 9.0.5? Jay Levitt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] psql -1 with multiple files?

Is there a way to load multiple .sql files in a single transaction? It looks like "psql -f file1 -f file2" or "psql -f file*" was a WIP patch that never happened, and from what I can tell, psql ignores the -1 parameter when reading from STDIN, so I can't cat them together either: $ cat > am_i_

Re: [GENERAL] psql -1 with multiple files?

Steve Crawford wrote: On 12/01/2011 02:01 PM, Jay Levitt wrote: Is there a way to load multiple .sql files in a single transaction? Alternately, since -1 basically wraps your input in a BEGIN...your statements...COMMIT you could do that yourself with a begin.sql and commit.sql: cat begin.sql

Re: [GENERAL] Controlling complexity in queries

Merlin Moncure wrote: Breaking your large queries into functions OTOH can make significant changes to the plan, often to the worse. As an end-user, I think this is an area where PostgreSQL could really stand out (that and the moon launch). In Rails-land, you don't have The DBA that writes qu

Re: [GENERAL] Controlling complexity in queries

e you run into difficult to optimize queries. (For my part, I'd just have converted that to WHERE EXISTS for the semi-join). I think I'm about to learn a very important relational-algebra equivalence... could you elaborate? Jay -- 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] Controlling complexity in queries

uot;but I want readability AND performance" issue is to 1. write a monolithic, optimized, incomprehensible version of the query 2. maintain the pretty functions alongside it 3. Write unit tests that confirm that the output of #1 and #2 is identical. Kinda like how gcc builds gcc and verifies tha

Re: [GENERAL] Correct syntax to create partial index on a boolean column

Craig Ringer wrote: it's a *bit* of a tiny use case. It certainly is. Jay -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 9.1.2: Preventing connections / syncing a database

ction. Can I do this while the database is live through some existing replication tool? The production database is on Ubuntu but the workstations are Macs, FWIW. Jay -- 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] 9.1.2: Preventing connections / syncing a database

loses focus, you refresh the browser, Rails automatically reloads the changed code, you see the change. (There are three different browser extensions that will automatically refresh your browser, too, in case that was too hard.) TL;DR: Reduce friction -> more frequent database updates -&

Re: [GENERAL] 9.1.2: Preventing connections / syncing a database

now will be too slow three months from now, so I want to start thinking ahead. Those are good ideas; I bet the pg_restore can be much faster with giant checkpoints, lots of memory, etc. I also see Bucardo's split_pg_dump, which would probably help - no point creating indexes on

Re: [GENERAL] Extensions btree_gist and cube collide?

IW, I saw this behavior when upgrading a 9.0 database (which had cube and earthdistance installed, but no cube or earthdistance objects in the database other than UDFs) and using the CREATE EXTENSION FROM unpackaged syntax. Jay Levitt -- Sent via pgsql-general mailing list (pgsql-general@postgresq

[GENERAL] High checkpoint_segments

r hot standbys. As a consumer-web startup, with no SLA, and not a huge database, and if we ever do have to recover from downtime it's ok if it takes longer.. is there a reason NOT to always run with something like checkpoint_segments = 1000, as long as I leave the timeout at 5m? Jay Levit

[GENERAL] Disabling triggers in a transaction

If I disable INSERT and UPDATE triggers inside a transaction; by setting and resetting reltriggers in pg_class; am I correct in thinking that this will disable triggers globally for that table for the duration of that transaction? So an INSERT or UPDATE to this table, outside of the transaction and

Re: [GENERAL] row numbering

OT: You have other database issues: http://www.gnumed.org/ error insert into WebLog values(586,31,"2005-02-28","") Duplicate entry '2005-02-28' for key 2 On Sun, 27 Feb 2005 18:08:02 +0100, Karsten Hilbert <[EMAIL PROTECTED]> wrote: > > > is there a way return a column with the row number autom

Re: [GENERAL] Copression

FWIW: If you use an stunnel or ssh connection of some sort, merely for compression and not security, the ARCFOUR encryption algorithm appears to have the lowest overhead & fastest throughput. Benchmarked it once for exactly this purpose. > > > It's a possible to compress traffic between server and

[GENERAL] Datatypes and performance

Couple of questions on datat types and performance 1. varchar vs varchar(2) I created a database schema and based on the test data I had to work with, I couldn't really determine the max size of a lot of string fields so I just left a lot of fields as varchar A comment from a coworker was that t

Re: [GENERAL] Transaction Blocks

t the whole block of transactions failing if one is duplicated. Best I could guess would be that you'll need to pre-process the data to ensure that the data is good. Take care, Jay ---(end of broadcast)--- TIP 2: you can get off all lists at once wi

Re: [GENERAL] Good open source mailing list system PHP / Postgresql

At 04:03 PM 12/01/2003 -0500, you wrote: > and I believe the the OOP features available in PHP 5, will easily leapfrog over Perl. As my daddy used to say "twice zero is still a small number" I'm a pretty big OO fan but when I use Perl I don't bother with the OO features once I had looked at th

Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL

> >If you need more power IMO Python is the way to go. > I am not that familiar with pything, not to get off topic here but what you >can do in python that you can't do in PHP? Well if they are both Turing complete, arguably not much :) It's not the 'what' it's the 'how' I'm equating Perl with

Re: [GENERAL] problem of upper/lower case in table names

ike to know who's fault it is ( mine or postgres ) >Thanks for your advices & help I've run into the same problem just creating a table with a name including an uppercase. The name gets converted to all lowercase but then I seem unable to DROP the table. >NB : i'm using postg

[GENERAL] Event-driven programming?

;-) Thanks for any help you can provide. JDG -- Jay Dickon Glanville ---(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] Event-driven programming?

On 9/12/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote: > > Hello all. > > > > Is it possible for PostgreSQL to notify me of a successful transaction > > commit? > > There i

Re: [GENERAL] Pgpool starting problem

om my iPad > On Sep 9, 2014, at 2:42 PM, "Ellen [via PostgreSQL]" > wrote: > > Hi Jay, > Can you pls tell me how you resolved this issue. > We are running pgpool-II version 3.3.3 > Thanks. > Ellen > > If you reply to this email, your messag

Re: [GENERAL] Pgpool starting problem

14 2:42 PM, Ellen [via PostgreSQL] wrote: > Hi Jay, > Can you pls tell me how you resolved this issue. > We are running pgpool-II version 3.3.

[GENERAL] Pgpool starting problem

the same behavior repeating. Any ideas? -- Jay -- View this message in context: http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general

Re: [ADMIN] [GENERAL] Storing database in WORM devices

ot support this very well, if at all. -j -- Jay A. Kreibich | CommTech, Emrg Net Tech Svcs [EMAIL PROTECTED] | Campus IT & Edu Svcs <http://www.uiuc.edu/~jak> | University of Illinois at U/C ---

[GENERAL] How to return a modified array from a user-define function...

TURNS _float8 AS 'SELECT {$1[1] + 1, $1[2] + $2, $1[3] + ($2 * $2)};' LANGUAGE 'sql'; This doesn't work. I get the following error: ERROR: parser: parse error at or near "{" Any help will be appreciated! -- D. Jay Newman ! For the pleasure and

[GENERAL] PG 6.1 Archive Needed

6.1 PG archive) to extract the data. Even Marc doesn't have a copy... If you have a copy of 6.1, please e-mail me. Thanks, Jay W. Summet

[GENERAL] FATAL 1:btree: BTP_CHAIN flag was expected (vacuum command)

The top command used to work (for the last few years), so I assume that something has changed with my data. Otherwise, the database appears to be working ok. Any pointers? I'm thinking about dumping all the data and re-inserting it... Jay Summet