In PostgreSQL 8 and up:
SELECT lastval();
gustavo halperin wrote:
Hello
I have a question, if I have a table with a SEQUENCE primary key, that
obviously, I doesn't give in each new row inserted. For example if the
table locks:
CREATE SEQUENCE id_seq;
CREATE TABLE table ( idintege
My development machine is PostgreSQL 8.1.5, and my production machine is
PostgreSQL 8.2. Until now I haven't run into any differences in
behavior. I have a query with a relatively wacky join, and while it was
working on my development machine, it wouldn't work on the production
machine. The
I have a table with a few million rows which has inserts performed on it
roughly 50 or so times a minute. It contains a heavily-queried column
that I would like to add an index to, but I am concerned about a
deadlock occurring. Should I wait until downtime to add the index, or
is the Postgres
Is there any overwhelming reason you can't just stick an apache server
on your DB server? Unless you expect this thing to get hit *hard*, the
performance of having them both on the same machine is pretty acceptable
(I know, everyone's opinion about what constitutes heavy usage
differs). If th
Here's a little tool I wrote:
http://www.hardgeus.com/projects/pgdesigner/
I have a Windows binary up there. The build process is currently a
mess, sorry.
It's a pretty decent little tool for quick-and-dirty visualization,
which is all I really use it for.
nyenyec wrote:
Hi,
Can anyone
If you end up storing the pictures on a separate database server for
performance reasons, Jorge's argument regarding integrity becomes
moot...And his argument so far is the strongest case I've seen for
storing the files in the database.
Scott Marlowe wrote:
On Fri, 2007-01-05 at 15:54, Jerem
My comment about stark raving madmen was somewhat tongue-in-cheek.
There is certainly a case to be made for images in a database under some
circumstances. However, for the average Joe web developer, and
certainly someone new to the whole problem, I think storing them on the
filesystem is goin
, a cron job pg_dumps every night and copies the dump over
to the backup DB server.
And before anybody gives me any guff, our office is in New Orleans, and
we went through Katrina with less than an hour of downtime, and without
losing anything. So there ;)
Jorge Godoy wrote:
John McCawley
Don't store your images in the database. Store them on the filesystem
and store their path in the database. Anyone that tells you otherwise
is a stark raving madman :)
My system is very heavily used, and our pg_dump is only a few gigs.
Meanwhile our images/documents storage is well over a h
I'm not trying to be argumentative, but I honestly don't know what you
mean here...and the only reason I ask for clarification is that you are
probably trying to tell me something important :)
Tom Lane wrote:
John McCawley <[EMAIL PROTECTED]> writes:
While this value isn&#
guaranteed to never
change, it is pretty dang close. I have a strict correlation between
the username that someone uses to login and the ID returned. Changing
someone's username would break a whole lot more than this one app on the
project...
Erik Jones wrote:
Tom Lane wrote
.
John McCawley wrote:
This is revisiting a problem I posed to this group a month or so ago
regarding separating different users' data through schema views. The
solution we're using is based on a suggestion we received here:
http://archives.postgresql.org/pgsql-general/2006-12/ms
This is revisiting a problem I posed to this group a month or so ago
regarding separating different users' data through schema views. The
solution we're using is based on a suggestion we received here:
http://archives.postgresql.org/pgsql-general/2006-12/msg00037.php
Everything is working gre
(I am working on this project with Derrick.) We have to use the Active
Directory to authenticate not only users from our client-side app (We're
attempting to use PostgreSQL essentially as a proxy authentication
mechanism), but also for connections to the SFTP server, and finally our
web app. Ra
tallshield and I need to pick up the files in Postgres
to include.
Bob Pawley
- Original Message - From: "John McCawley" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Postgresql"
Sent: Monday, December 18, 2006 10:57 AM
Subject: Re: [G
I'm not quite sure I understand your question, do you mean that you wish
to distribute an "all-in-one" installer for a client machine that will
include both your app and PostgreSQL?
It all depends on the complexity of your database backend. If this is
small, quick-and-dirty app, I don't know
sslmode=require did the trick. Thanks!
Dave Page wrote:
John McCawley wrote:
Where would I go to find the connection string settings to turn it
on? I have dug around quite a bit, and tried everything I have found,
but nothing seems to work, and the docs included with the driver
don't
Where would I go to find the connection string settings to turn it on? I
have dug around quite a bit, and tried everything I have found, but
nothing seems to work, and the docs included with the driver don't seem
to cover it.
Dave Page wrote:
John McCawley wrote:
I did a few search
I did a few searches on the mailing list, and checked the documentation
of psqlODBC, and I'm confused as to the current status of SSL support in
psqlODBC. From the mailing list, it appears that the Open Source driver
does not fully support SSL, and I would need to use the commercial ODBC
drive
A bunch of people have asked me about it, and I finally have a Windows
machine sitting around with QT, so I built a Windows binary for
pgDesigner that you can get here:
http://www.hardgeus.com/projects/pgdesigner/pgdesigner_win_0.8.zip
It's a pretty useful little tool for visualizing existing
Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!
Niklas Johansson wrote:
On 1 dec 2006, at 15.19, John McCawley wrote:
That's
That's the first idea I've seen that looks like it might actually
work... (Not that the other ideas were bad, but I just couldn't see how
I could fit the solutions into my current app)
So what would my user setup look like? Would it look something like this:
createuser joe
grant select on sch
Why does user big_daddy need to access everybody's data? Who is he?
What's his role? It seems like a big security problem waiting to
happen, but that's just me.
Uncle Sam :)
This is one of those fundamental problems you run into when you make a
design decision up front (user perms in th
Maybe I'm not understanding what you're getting at, so I'll throw out an
example:
--
With my current architecture, smartlowe logs in, but his login is
handled at the application layer, so his database connection is simply
"foo". He inserts a hundred records in the invo
system to also pull
from this additional schema, or modify my entire application to pull
from views which must be modified every time a company is added...
Rodrigo Gonzalez wrote:
search_path="$user" in postgresql.conf
and you create one schema for each user with the user name a
It seems that this approach would suffer the same problem as the one I
outlined in "1) Actually separate client data by table". I would have
to modify the logic of my web app...My web app currently handles all of
the data, regardless of company, so it would have to aggregate the data
from the
I have been using PostgreSQL for years in my web apps, and so far in my
career I have not had to deal with database-side permissions issues.
i.e. when I have multiple clients, or hands on the data, everyone
interfaces through my web app and I handle security there. The app
enforces what data
I can't speak for wxPython etc., but regarding using QT from C++, your
speed of development depends on your C++ skills. I wrote a
full-featured Point of Sale system in Visual Basic that has been in
active use for about 4 years. The original development effort (screens,
grids, etc.) took about
Well, to be fair, I was using fairly low-level functionality. If you
look at the screenshot from my app, I am just using the raw window class
and handling all of the dragging etc. manually. In addition, my foreign
key drawing is handled by directly painting on the background, and my
own click
I have been struggling with this problem for a few years. I have
written a basic relational database design tool:
http://www.hardgeus.com/pgdesigner/
I have completely rewritten this program 3 times. I initially
implemented it in Fltk, but ran into a bunch of limitations in the API.
I rewr
I promise I'm not trying to be a pain in the butt ;) Do you then use
your serial id as your foreign key in other tables, or the
firstname/lastname primary key?
Joshua D. Drake wrote:
users
=
id serial unique,
first_name text,
last_name text,
primary key (first_name,last_name)
Yes there
This list represents the most informed database admins I know, and while
the conversation can easily devolve into minutae, I am genuinely
interested in everyone's opinions on this subject. Primary keys are a
pretty central aspect of database design, and most everyone on this list
has unique ex
Right, but when you write your script (or whatever) that cleans these
keys up and does the merge, you're where you started -- arbitrary
integer keys with no meaning. If you merge databases where the keys are
*supposed* to have meaning, you then have to mangle *real* data to make
them merge.
Yes, that's the point. They are legacy designs, and that portion of
the design is wrong.
I'll weigh in my my .02 on this subject. After much pain and agony in
the real world, I have taken the stance that every table in my database
must have an arbitrary, numeric primary key (generally au
ot; apps) is PHP/Postgres.
Tony Caduto wrote:
John McCawley wrote:
I'm not really sure what switching development platforms for the tool
would buy me. Are you saying that the wire-protocol used by vb.net
and C# etc. is substantially faster than what is available in VB?
Regarding ASP.net, I
I'm not really sure what switching development platforms for the tool
would buy me. Are you saying that the wire-protocol used by vb.net and
C# etc. is substantially faster than what is available in VB? Regarding
ASP.net, I don't really see how a web app would help here, as it has to
run on h
I have found myself somewhat trapped in a project where the software
being used is essentially set in stone, and its limitations are
strangling progress. It is a client-side app written in VB with an
Access backend, and it has no reasonable data migration/synchronization
ability. It was desig
That looks like the solution to my problem, thanks!...I tried running it
on my 8.0.8 server, but it wasn't found, I assume that's an 8.1 only
function?
Martijn van Oosterhout wrote:
On Tue, Nov 14, 2006 at 03:54:42PM -0600, John McCawley wrote:
I am once again dealing with th
I am once again dealing with that age old problem of retrieving the
value of your inserted serial field in VB. I am fully aware that I can
manually pull the currval or nextval from my sequence, but I consider
having to manually deal with an auto-generated database object an
*extremely* inelega
Yeah, apparently it's OK when spewed out to the browser...Didn't think
of that :P
Martijn van Oosterhout wrote:
On Tue, Sep 12, 2006 at 10:56:09AM -0500, John McCawley wrote:
I've searched high and low, and all I've found is people being chided
for trying to conver
I've searched high and low, and all I've found is people being chided
for trying to convert from Bytea to text :)
When I first designed my database, I simply didn't understand the
purpose of bytea, I didn't actually realize that there *was* a text data
type. (Actually, I was porting from a MS
Earlier this week when I logged into my database, I just so happened to
notice that the value for now() was six hours off of the time reported
by the operating system when using the date command from the command
line. How is this possible? I rebooted the server and the problem
magically went
surabhi.ahuja wrote:
but if i have "on delete cascade" constraint,
in that case if i have a trigger which is fired in case delet happens
on the table y.
i have a table x, and table y has a foreign key with "on delete
cascade" constraint,
now i delete a row from x, will the trigger still
Foreign keys do not cascade deletions. If table y references table x on
column a, the attempt to delete records in x with dependent records in y
will yield an error. So the answer to that question is no, your trigger
won't get called because a) y doesn't get touched because that's not
what fo
I really wouldn't take relative DB performance into much consideration
when choosing a programming language for your project. I have found
that the actual Language/API overhead to be pretty inconsequential in
most of my projects. When my DB access is slow, I have found it is
almost always a p
OK, one last question on the subject and I'll shut up. I would assume
that all dependent database objects are also dropped when you drop the
table, so you'd have to recreate all of your foreign keys (both
directions) and triggers etc.?
Andreas Kretschmer wrote:
John McCawl
I would certainly never have any of my normal application code depend on
the ordering of columns. My desire for column ordering is strictly for
administration purposes, keeping the visual clarity of the datamodel,
and ensuring that the visual datamodel is consistent with what is
actually in th
database
level, and I'm sure there are sound technical reasons why it hasn't been
implemented, but I do believe that it is a desirable feature.
Berend Tober wrote:
John McCawley wrote:
Is there a way to change the order of columns in a table in Postgres
after it has been created?
Is there a way to change the order of columns in a table in Postgres
after it has been created? Back when I used MS SQL Server, it was
trivial to go into Enterprise Manager and move columns up and down. I
find this a desirable feature, as I like to keep like columns grouped in
my table. Some
I have decided that I'm going to resume working on my pgDesigner project
( http://www.hardgeus.com/projects/pgdesigner/ ). I haven't really
maintained it for over a year, but I get about 200 visits a day to the
project homepage, and about two or three emails a week asking for help.
As the pro
It looks like my primary slowdown on that query was the timestamp
trigger. However, even after removing that trigger, and ensuring that
all of my referencing tables had their foreign keys indexed, a simple
update of one column on 244451 records took 14 minutes. Given the specs
I mentioned in
Tom Lane wrote:
The differential would have to be index updates or triggers fired by the
UPDATE. I'd bet on
the latter, but since you've told us zip about your schema or what PG
version this is, it's impossible to speculate further...
This is my development machine. I'm running PostgreSQ
I am currently having a problem with a query never finishing (or at
least not in a reasonable amount of time.) I have had similar problems
to this in Postgres over the past several years, and I have always found
workarounds. This time I'd like to actually understand what is happening.
I have
Well, the rule is very simple, either always quote your identifiers, or
never quote them. If you always quote them, then you always need to
specify them in the same case. If you never quote them, then you always
get case-insensetivity.
My memory is a little hazy, as it's been a few years, but
Due to case weirdness in Postgres, particularly when accessing it from
PHP, I completely abandoned mixed case table and column names. I don't
know if things have changed in the past several years, but when I first
made the leap, mixed case was more trouble than it was worth.
Even after I got
You should be able to use my trick...the join that is giving you the
problem is:
SELECT *
FROM
tokens.ta_tokenhist h INNER JOIN
tokens.vw_tokenst ON h.token_id = t.token_id
WHERE
h.sarreport_id = 9 ;
ta_tokenhist is already part of your view, right? So you should be able
First of all, check out this thread:
http://archives.postgresql.org/pgsql-general/2005-11/msg00734.php
I had a similar performance issue with a view. Look at my solution and
it might help you out.
Second, you might want to change your token history status from a string
to an integer that re
I wrote a little app that does basically what you're looking for:
http://www.hardgeus.com/projects/pgdesigner/
It's not professional quality, and is a little flaky, but it gets the
job done. It has a wxGTK dependency, and of course postgres. Your best
bet for running it is to pull it from my
I wrote a little LGPL app that generates Postgres SQL code from a
graphical datamodel. It's not professional quality, but it functions.
I don't actively maintain it, but I still use it for brainstorming
datamodels and quick generation of the database.
http://www.hardgeus.com/projects/pgdesig
> I'm not clear how to move that "person_role.person = person.id" into
the FROM statement. Does it matter?
This should work:
FROM person
INNER JOIN person_role ON person.id = person_role.person
LEFT OUTER JOIN instructors ON (person.id = instructors.person)
LEFT OUTER JOIN class ON (instructor
ific problem, but I
generally find that kind of mixed syntax muddles a query.
John McCawley wrote:
Well, I'm stabbing in the dark now. You mean like:
SELECT person.id AS id, first_name, last_name,
count(instructors.class) as total_classes,
sum (CAS
It looks to me like your problem is that weird area where you alias your
inner join as "t" and thenn inner join based on this alias. You're
getting a cartesian product somewhere, as evidenced by the
"rows=700" in your explain.
I already deleted the old mail with your table structure, but tr
I'd be curious to see what would happen if you added claimnum as a
field in your view. I don't have a complete understanding of the
postgres internals in terms of how it is able to push outer clauses
down in to its views, but I think it might be able to optimize in
that fashion if it is a
Hello all,
I have a fairly complex query whose performance problem I have isolated
to a fairly small subset. The pertinent parts of the table structure
are as follows:
//-
tbl_claim
claim_id integer SERIAL PRIMARY KEY;
claimnum varchar(32);
//---
64 matches
Mail list logo
