An alternative, though I'm not sure it's better in any way :
DELETE FROM generators WHERE started + '30 minutes'::interval <= now();
-Mitch
- Original Message -
From: "Paul Tomblin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, July 08, 2001 2:12 PM
Subject: [GENERAL] How sh
> For a long time, I thougt HOWTO is docuement for "quick start". Isn't it?
I'm not sure there is an understood meaning of what a HOWTO actually it.
It's an explanation of how to do something, I guess... Generally I've found
(as in your example) that it's putting software together and making it w
> 1. I find about 50% database storage overhead in this case. That's not
completely silly, considering this is structured data, but seems a little
high. I don't know >the internal structures well enough to really see
what's happening.
Hmm, the PG docs say to expect data stored in the database t
No.
- Original Message -
From: "jose" <[EMAIL PROTECTED]>
To: "Postgres" <[EMAIL PROTECTED]>
Sent: Wednesday, August 22, 2001 3:51 AM
Subject: [GENERAL] maximum query length
> Hi all,
>
> I have a problem related with the maximum query length
> ERR: query is too long. Maximum length
- Original Message -
From: "Sean Chittenden" <[EMAIL PROTECTED]>
To: "Bruce Momjian" <[EMAIL PROTECTED]>
Cc: "Gowey, Geoffrey" <[EMAIL PROTECTED]>; "'Dr. Evil'"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, August 25, 2001 11:48 PM
Subject: Re: MySQL's (false?) claims... (was:
> Yup. We wrote the client that is accessing the database. It's using
> PHP, and we don't even *use* transactions currently. But that isn't the
> problem. From what I gather so far, the server is under fairly high
> load (6 right now) so vacuuming the database (520MB in files, 5MB dump)
> take
> 2) More importantly, is it possible to prevent a customer from peeking
into
> said database once it is deployed on their machine? A large part of what
> makes my application proprietary is the data model in the database, and
it'd
> be tough to maintain a competative edge when everyone can see e
> 1. I thought the SQL spec required varchar() not to pad. Is it
> just that, because of the way pg_dump saved the char() data (as
> blank-padded) that the varchar() field preserves the padded data?
A dump from a char() field keeps the NULL padding even in the dump file I
assume, so when you wen
> That's not nonsense at all, you can't just go around and redefine the
> language used in the database world at your own whims.
"Stored Procedure".. Hmm, that seems to me that the definition of that would
be "a procedure that's stored somewhere". When talking about stored
procedures and database
> I'm trying to execute the following query:
> "SELECT TOP 10 * FROM table1"
SELECT * FROM table1 LIMIT 10
-- might give you what you're looking for.. I've not seen TOP, though I
could guess where it comes from (Oracle?) -- top seems to imply order so you
might want to ORDER BY some
The 8k (well, BLCKSZ limit) has been eliminated for quite some time now..
-Mitch
- Original Message -
From: "Philip" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, September 15, 2001 6:57 AM
Subject: [GENERAL] Does Postgresql 7.1.x Still Have 8K Maximum Field Size?
> Hello
SELECT * FROM apples WHERE lower(color) LIKE '%red%';
Make sure that what ever 'red' might be is always lower case (you can pass
it through lower() too, of course)..
-Mitch
- Original Message -
From: "Michael Gay" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, September 10,
Use psql -E and it will give you the SQL for the backslash commands..
-Mitch
- Original Message -
From: "Arcady Genkin" <[EMAIL PROTECTED]>
To: "PostgreSQL" <[EMAIL PROTECTED]>
Sent: Wednesday, October 17, 2001 4:35 PM
Subject: [GENERAL] SQL equivallent to "\ds" in psql
> Where does
In order to take advantage of some new hardware I had to use Linux over
FreeBSD.. I use to run my backend with the options -B 4096 -o '-S 16384' but
remember I had to modify the FreeBSD kernel to allow processes to use that
much shared memory... How would I go about doing that in Linux? I've been
databasename=# explain select DISTINCT (case when resubmitted > created then
resubmitted else created end),a.app_id, a.appcode, a.firstname,
a.middlename, a.lastname, a.state, a.degree1, a.d1date, a.degree2, a.d2date,
a.salary, a.skill1, a.skill2, a.skill3, a.objective, a.employer, a.sic1,
a.sic2,
- Original Message -
From: "Nathan Barnett" <[EMAIL PROTECTED]>
To: "'Mitch Vincent'" <[EMAIL PROTECTED]>
Sent: Friday, July 21, 2000 3:03 PM
Subject: RE: [GENERAL] Sort
> Here is the query:
>
> SELECT Impression.AdNu
Well, you will have to escape the single quotes but you shouldn't have to
escape the newlines, I'm inserting some chunks of text that have all sorts
of newline characters and I'm not escaping them, it seems to work fine..
-Mitch
- Original Message -
From: "Alan Horn" <[EMAIL PROTECTED]>
I don't think it's random (well, I'm sure it's not) but you could use LIMIT
to get a smaller number of results...
*shrug* just an idea.
Good luck!
-Mitch
- Original Message -
From: "Nathan Barnett" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 24, 2000 3:20 PM
Subject:
I've been using PHP with PostgreSQL as long as I've been using either, what
is the error message exactly?
Did you install the libraries in something other than the default
directories? You can configure PHP with --with-pgsql=/path/to/pglibs if you
did..
-Mitch
- Original Message -
From
The reason I didn't have to use the -l is that I have everything PostgreSQL
needs (as far as environment variables) already set, because this machine is
a dedicated PostgreSQL server.
Sorry, should have thought about that before I replied but it seems you have
it running now, that's great.. Good
If there isn't any kernel panic (or other error message) displayed then I'd
bet it's a memory problem (it's almost certainly a hardware problem),
PostgreSQL is just triggering it (probably by using the damaged memory).
I've seen this happen before with other programs... Try switching out the
RAM a
I too use PHP and PostgreSQL for %90 of my web programming projects (C
for the other %10)... I find the embedded nature of PHP to aide in
development more than anything (though I suppose you can get that from
mod_perl as well).. I don't do much Perl at all, I found the language to be
sloppy an
> Hi,
>
> I'm pretty new to PgSQL (been lurking) and am wondering if
> it's possible to talk with other DB's easily? I'm using
> FreeBSD atm and the project I'm working on will be moving
> from dbase to postgres in the next release. It will also
> have a web based side to it that I plan on using
> > 1. How often DOES PG flush to disk - if at all - when the -F option is
> > invoked? Can this be controllled?
>
> Once after each transaction.
That's what it does when -F is *not* used, right? -F disables calling
fsync() after each transaction, right?..
-Mitch
Hmm, it seems we all know just enough to be dangerous :-)
I have seen many threads on the "to fsync() or not to fsync()" and
overwhelmingly people have come out and said that to not fsync() is A Bad
Thing(TM). -- If Neil is right then it being bad or not is going to depend
very much on the files
What is the main concern? That Great Bridge or PostgreSQL Inc will try to
influence development? This is just my lowly opinion but it seems to me
that this could be a storm brewing in a tea cup, it just doesn't seem to be
that threatening a situation at a glance.
Congrats to everyone on their ne
Maybe I'm just overlooking something really simple but this has me a bit
confused.
What I'm trying to do is get the amount of time from A to B -- I thought
age() would do just that but it seems to be about a day off sometimes.
hhs=# SELECT age('Sun Dec 03 08:00:00 2000 EST','Tue Oct 10 08:00:00
I should point out that it works as I expected it to on other values..
hhs=# SELECT age('Sun Nov 05 08:00:00 2000 EST','Tue Oct 10 08:00:00 2000
EDT') as esec;
esec
--
@ 26 days 1 hour
(1 row)
hhs=# SELECT ('Tue Oct 10 08:00:00 2000 EDT'::timestamp + '1 mon 24 days 1
hour
No matter where you choose to buy it, do buy it and support Bruce,
PostgreSQL and the publisher for allowing it to be electronically
published!!
-Mitch
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Steve Wolfe" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, October 11
> Edmar Wiggers wrote:
> >
> > I too am interested in full text indexing under PostgreSQL.
>
> As I have described it, does it sound like something you would use? It
> is designed more like a search engine. It will do a full text / boolean
> search with phrase ranking in about 10~40 ms depending o
I haven't used PL/pgSQL very much but it looks like a good language in which
to make some simple functions for this application I'm writing..
Is it possible (with PL/pgSQL) to access other records in other tables than
the tuple that pulled the trigger (and called the function)?
Say this (pseudo
Er, I'm pretty sure I found what I was looking for, sorry to waste
everyone's time.. I looked right past half the documentation!
-Mitch
- Original Message -----
From: "Mitch Vincent" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 22
everything
(total,amount and 0.00) to float and everything to numeric with the same
error popping up.. What needs casting here?
I can determine if an invoice has been paid or not a number of ways, really
what I should do there is NEW.amount >= total -- I tried and got the above
error again..
ce_id = NEW.invoice_id;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Thanks again for answering my stupid little questions, Tom :-)
-Mitch
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Mitch Vincent" <[EMAIL PROTECTED]>
Cc: <[EMA
There is some information here :
http://www.postgresql.org/devel-corner/docs/postgres/release.htm
- Original Message -
From: "David Wall" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 24, 2001 1:07 PM
Subject: 7.1 expected features list?
> Is there a place where
> My /etc/rc.d/init.d/postgresql script has this line:
> su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o
> '-B 4096 -i' start >/dev/null 2>&1"
Isn't that BLCKSZx4096 ? I'm almost sure it is..
Some people (me) have BLCKSZ set to 32k so Just an FYI.
-Mitch
> > the max size of a row...
>
> 8k in pre v7.1, no limit in v7.1 an dlater ...
32k really... BLCKSZ can be changed.. I've had no trouble running a
production database with BLCKSZ set to 32k though there might be issues I'm
not aware of..
-Mitch
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Mitch Vincent" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, January 28, 2001 1:39 PM
Subject: Re: [GENERAL] varchar => int
>
> Well, you can get there via text (as Peter
This is simple, but it's what I use to do my daily dumps (run from
crontab) -- I took out some stuff specific to my application..Replace
database with the name of the database..
#!/bin/sh
pgpath=/usr/local/pgsql/bin
homepath=/home/postgres
backup=/usr/local/pgsql/backup
today=`date "+%Y%m%d-%H%
> Hi,
>
> OK full text searching. Will the full text index
> catch changes in verb tense? i.e. will a search for
> woman catch women?
>
> I'm researching before I dive in to this later in the
> week so please excuse this incompletely informed
> question: Will I need to rebuild postgresql with t
Another thing..
Full text indexing, last time I checked, was just a trigger/function, you
don't have to rebuild anything that I'm aware of to include it..
-Mitch
> Hi,
>
> OK full text searching. Will the full text index
> catch changes in verb tense? i.e. will a search for
> woman catch wome
Super sweet... That is excellent.
-Mitch
On Thu, 1 Feb 2001, Bruce Momjian wrote:
> [ Charset ISO-8859-1 unsupported, converting... ]
> > Hi Doug, your comments caught my eye and I thought I'd ask you something..
> > Are you speaking of using persistant connections with PHP? I'm not sure wha
In addition to being too purple you might want to note your database is too
old! Upgrade to 7.0.3 - it has foreign key support..
-Mitch
- Original Message -
From: "Adam Haberlach" <[EMAIL PROTECTED]>
To: "" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, February 06, 2001 12:5
> Well you wouldn't want to start building these kind of rules in your
> application - better to have them in the search engine. The fulltextindex
> in the contrib package does of course not offer anything like this, it
> would be nice to see a third party addon provide fulltext capabilities for
> I'm fairly new to PostreSQL, coming from MySQL. My Python application
> generates these MySQL commands:
>
> drop database if exists Foo;
> create database Foo;
> use Foo;
> Using the PostgreSQL online docs, my closest translation is:
>
> drop database Foo;
> create database Foo;
You supply the
hhs=# INSERT INTO applicants_test (SELECT app_id ,old_id ,emp_id ,inv_id
,createdate ,updatedon ,todelete ,appstatus ,apptype ,infosent ,empinitials
,firstname ,lastname ,salutation ,fontype1 ,fonnumber1 ,fonext1 ,fontype2
,fonnumber2 ,fonext2 ,fontype3 ,fonnumber3 ,fonext3 ,address1 ,address2
,ci
Hey guys, another strange question here..
If I query and order by a field and there are duplicate values in that
field, what makes one return before the other? Just the first one that PG
comes to on the disk is displayed first or is something else looked at to
determine the order?
Example :
hhs
Thanks again!
-Mitch
- Original Message -----
From: "Mitch Vincent" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 5:54 PM
Subject: Order question
> Hey guys, another strange question here..
>
> If I query and order by a field and there are d
ent: Tuesday, February 13, 2001 6:18 PM
Subject: Re: Re: Order question
> On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote:
> > I just set the row with j.inv_id to 1, I'd like it to be ordered above
the
> > row with j.jobtitle in it -- is that possible?
>
&
> Yah. What I find is the developers set the general tone/culture of the
list.
> This affects the type of responses/support you get even from the other
list
> subscribers. So it's quite good here where you have kind and helpful
developers.
Helpful developers doesn't go near far enough..
I've see
Can you EXPLAIN that query and send us the results (the query plan)? That
should tell a whole lot.
-Mitch
- Original Message -
From: "Dave Edmondson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, February 16, 2001 1:32 PM
Subject: Postgres slowdown on large table joins
> I'
> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> sav
Are you referring to short circuit? That's a language feature, isn't it? I
didn't think it had anything to do with the compiler (I know C and a few
other languages do it). Anyway, I could be wrong.. Seems that could break a
lot of code if the programmer relies on short circuit in some conditional
In pre 7.0 versions there was an 8k query size limit and in all pre 7.1
versions a limit on the size of a field (BLCKSZ from 8k (default) to 32k)...
I'd say it's either in there to prevent one or the other.
Of course 8140 isn't quite 8k but *shrug*..
-Mitch
- Original Message -
From:
Just an FYI.
> FWIW, I emailed the php maintainer of the postgres piece, and he replied
> with the following (I installed the patch, recompiled, and everything has
> been running fine for about a week or so)
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Tole
If you're talking about some kind of C application then it's going to be
portable only you'll have to rip out the PG API and replace it with the
Oracle API, I'm sure that's not such an easy task..
If you're speaking of an application written in say, PHP, then that's a
horse of a different color.
Oh and the instructions in the INSTALL file (under the directory where the
source was un-tar'd) is very easy to follow and walks you through the
installation as much as possible IMHO..
The user/admin/programmer manuals and the tutorial @ www.postgresql.org are
all good too, they detail just abou
t; Rod Taylor
>
> There are always four sides to every story: your side, their side, the
> truth, and what really happened.
> - Original Message -
> From: "Mitch Vincent" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, March 02, 2001 1
Upgrade to 7.1 as soon as it's out (shouldn't be long now)..
I successfully migrated a 6.4 database to a 7.1 database while 7.1 was in
the early beta stages, a few minor problems but the entire process only took
half an hour to complete..
Good luck!
-Mitch
Software development :
You can have it
If you could post the schema of your tables that you do the query against
and an EXPLAIN of the queries you're doing, perhaps we could further tune
your queries in addition to beefing up the memory usage of the backend..
Check this link out too.
http://postgresql.readysetnet.com/devel-corner/docs
I'm not sure about database names but table and field names can be upper
case (or contain upper case characters) you just have to reference the
table/field in double quotes..
Example :
SELECT * FROM "FoBaR";
Hope that helps.
-Mitch
Software development :
You can have it cheap, fast or working.
7.1 is a release (the latest), 7.1rc4 is a *Release Candidate*.
-Mitch
Software development :
You can have it cheap, fast or working. Choose two.
- Original Message -
From: "Matthew Hixson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 18, 2001 1:28 PM
Subject: ver
> I suppose it isn't a major problem, but enforcing strict grammar
> helps to show up inadvertent errors. Suppose I have a set of schema
> building files for a whole system; the way I do things, there may be fifty
> or more files, one per table. If one of these gets corrupted in editing
> (perha
> So, I am not really sure what is the benefit of writing logic inside the
DB.
> Is there a performance benefit compared to processing via PHP?
One key benefit aside from anything else would be the ability to call
the stored proceedure no matter how you were interfacing to the database.
I've
Uhh.. OS wars are silly. Use what ever OS you like.
We should discuss PostgreSQL here, not operating systems (at least not in
the classic flame-war style)..
*sigh*
-Mitch
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [
You *really* should go glance over the manual.. There is the field type
SERIAL and things called sequences, both could be what you're talking
aboutThe rest I'll leave up to you :-)
Good luck!!!
-Mitch
- Original Message -
From: "Clay & Judi Kinney" <[EMAIL PROTECTED]>
To: <[EMAIL PR
> I'm trying to store anywhere from a few words to a half page of text as a
> field in my table. A fellow db programmer told me that better than using a
> varchar is to break the message up into n pieces of size m (let's say
m=100),
>
> and make n (or n+1) varchar(m)s, and relate them together.
I
desc is a reserved keyword (used in ORDER BY to indicate descending order)..
You can use keywords as field names though you have to put the in quotes (as
you found out!).
-Mitch
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 10, 2001 2:08 PM
S
Does that query really return 9420 rows ? If so, a sequential scan is
probably better/faster than an index scan..
-Mitch
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 10, 2001 9:22 AM
Subject: Re: Query not using index
> I vacuum every half
1,000 is one thousand, right?
1.000 is one, right?
Since the decimal isn't just an arbitrary separator when we're speaking
of decimal numbers (1.00 is certainly a lot different than 1000.00) I guess
I don't follow what the problem is.. How can you have one thousand
represented as 1.0
Ok, over my head -- someone has schooled me.. My apologies for the list
noise.
-Mitch
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
> Is it possible to create an index using a function(field) sintaxis ?
As far as I know you can -- I have lots of indexes on lower(varchar).. There
may be limitations though so I'll let someone else have the final word :-)
-Mitch
---(end of broadcast)--
72 matches
Mail list logo
