[GENERAL] Postgresql Instability 2
I'm not telling that I've chanced this or that: I am telling that only with compiling the postgresql7.0 updating the RC1 it give me this (with the same SETTINGZ pgdata,pglibs,sqladmin,etc... the SAME!) on an other machine the 7.0definitive go directly in core dumped with kernel 2.2.14 and gimme the precedent problem in 2.3.x and this with the Mandrake 7.0 and Red Hat... I need urgently a solution... because I am planning to create an informatical system in my enterprise based on postgresql7.0 and a gnome application written in C (PS: the libpq are great! simply and powerfull!!) for get the ISO-9002 certification! please Help Meah! :-) Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[GENERAL] Re: [ANNOUNCE] PostgreSQL 7.0 a success
First of all, Let me say I dont have much experience with postgresql and I've done only few tests, so excuse me for any wrong comments i make. I think the following things should be improved in postgresql now: - reliability - Documentation Sometimes a table doesnt exist anymore but it's still listed in pg_class table, or the opposite, or you did a physical backup and you wanna restore the db, and other things that could be improved and more documented. Some crashes we tested (like powering down the system while running with flush off) were just fatal to some tables, and after restart we got the 'backend terminated...' message when trying to use the table. We also tried a dump after restarting, but other processes that started after the dump were frozen, waiting for the dump to complete. I also miss something like mysql's isamchk and a better control of the security, with 1 system table controlling passwords, hosts allowed and denied, and anything for the users of all databases. just my 2 cents.. regards, At 23:20 20/05/00 -0400, Bruce Momjian wrote: >PostgreSQL 7.0 has been a huge success. Bug reports since release have >been so few that we thought our e-mail system wasn't working properly. >Turns out things are very quiet because the release is so stable. > >So, those people waiting on the fence to try 7.0, go ahead. It is >ready for prime-time. > >Of course, we have big plans for 7.1, and will start on that shortly. > >-- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > Gustavo Henrique Maultasch [EMAIL PROTECTED]
Re: [GENERAL]
Ooops. I have to withdraw that comment. I spent hours the other day beating my head against the wall over this. I was sure that it didn't work . . . Sorry, Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer On Wed, 24 May 2000, Ross J. Reedstrom wrote: > On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote: > > One problem you may have with this is that if a function accesses some > > table, the user who uses that function must also have permissions on the > > table. I have a similar problem. I'd like to give permissions on a view, > > but not on the table underlying the view (the view serves to filter out > > some records the user shouldn't see). I can't give permission to use view > > without giving permission to use the table. > > Have you tried it? This is one of the things views are for. The view > accesses it's underlying tables as the user who created the view, as far > as I recall. I, for example, have an entire database where every table > has a 'pub' boolean. I've created views that return only rows with pub = > 't', and given the anonymous user (which the web server connect as) > select privileges only on the view. >
Re: [GENERAL] PG 7.0 is 2.5 times slower running a big report
Try putting 'nofsync' in your pg_options. Bryan White wrote: > > I have a large report that I run once a day. Under 6.5.3 it took just over > 3hrs to run. Under 7.0 it is now taking 8 hours to run. No other changes > were made. > > This is on RedHat Linux 6.2. A PIII 733 with 384MB Ram, and 2 IDE 7200 RPM > disks. One disk contains the Postgres directroy including the data > directory, and the other disk has everything else. > > The Postmaster is started with these options: -i -B 1024 -N 256 -o -F > > The report is being run on a backup server just after the database has been > loaded from a dump and 'vacuum analyse'd. There is practically nothing else > running on the box during the run. > > The report creates four separate concurrent cursors. Each of the queries is > sorted by a customer number which is an index. The index is unique in the > customer table but not the others. For the other cursors it pops values as > needed to process data for the current customer number. There are also > other selects that are run for each customer order processed to retrieve its > line items. The report does not update the database at all, it is just > accumulating totals that will be written out when the report finishes. > > Top tells me the front end process is using 5 to 10 percent of the CPU and > the back end is using 10 to 20 percent. The load average is about 1.0 and > the CPU is about 80% idle. I am prettry certain on 6.5.3 that the CPU usage > was much higher. Its almost like the new version has some sort of throttle > to keep one backend from saturating the system. Indeed the box is much more > responsive than it used to be while running this report. > > Suggestions?
Re: [GENERAL] PG 7.0 is 2.5 times slower running a big report
"Bryan White" <[EMAIL PROTECTED]> writes: > Top tells me the front end process is using 5 to 10 percent of the CPU and > the back end is using 10 to 20 percent. The load average is about 1.0 and > the CPU is about 80% idle. It's probably waiting for disk I/O ... What does EXPLAIN tell you about how the queries are being executed? Do you by any chance have the 6.5.3 system still available to compare its EXPLAIN output? regards, tom lane
Re: [GENERAL] 7.0 installation problem, help please :-(
Travis Bauer <[EMAIL PROTECTED]> writes: > That's odd. This is the error I got compiling pgsql 6.5 on Solaris. I > never resolved the problem. However, the 7.0 source did not give this > error. Maybe this is a stupid question, but are you sure you have the > most recent source code? > On Tue, 23 May 2000, Chris Chan wrote: >> stringinfo.c: In function `appendStringInfo': >> stringinfo.c:104: `va_list' undeclared (first use in this function) >> stringinfo.c:104: (Each undeclared identifier is reported only once >> stringinfo.c:104: for each function it appears in.) This would seem to indicate that isn't getting included, which in turn suggests that the configure script didn't define STDC_HEADERS (look in include/config.h to confirm or deny that). The autoconf manual lists a number of reasons for not defining STDC_HEADERS: - Macro: AC_HEADER_STDC Define `STDC_HEADERS' if the system has ANSI C header files. Specifically, this macro checks for `stdlib.h', `stdarg.h', `string.h', and `float.h'; if the system has those, it probably has the rest of the ANSI C header files. This macro also checks whether `string.h' declares `memchr' (and thus presumably the other `mem' functions), whether `stdlib.h' declare `free' (and thus presumably `malloc' and other related functions), and whether the `ctype.h' macros work on characters with the high bit set, as ANSI C requires. Any reasonably recent Unix system ought to pass those checks AFAIK, but maybe there's a screw loose somewhere... regards, tom lane
Re: [GENERAL] Postgres Instability
"planx plnetx" <[EMAIL PROTECTED]> writes: > FATAL 1: cannot create init file > mydatabasedirectory//base/mydb/pg_internal.init If you're getting that, there is something *seriously* broken --- the only way that can come out is if Postgres is unable to create that file when it wants to. I wonder if you are running the postmaster as the wrong user (eg, one without write permission on the database directories)? Another possibility is that you're running with an incorrect database path (postmaster -D switch or PGDATA environment setting). If that's an accurate transcription of the error message then it looks like your path may be messed up... regards, tom lane
Re: [GENERAL] LEFT OUTER JOIN?
Peter Landis wrote: > > Hi- I'm a newbie at postgres but have a pretty good > understanding of SQL statements. I have created two > views and wanted to do a LEFT OUTER JOIN of the two > tables. The sytax is > > select * from vcompany LEFT OUTER JOIN ON > vcompany.id=vregistry.id; > > I get the following error: > LEFT OUTER JOIN not yet implemented > > My question is does postgresql 6.5 support Left out > join and if not is there another way of implementing > this sql statement to give me a LEFT OUTER JOIN. > > The logic if very simple. Look below to see the > tables: > > vcompany > ++---+ > |id | Name | > +---++ > | 1 | Peter | > | 2 | John | > | 3 | Joe | > | 4 | Jerry | > | 5 | Mike | > ++---+ > > vcompany > ++-+ > |id | Desc| > +---+--+ > | 1 | A | > | 2 | B | > | 5 | D | > ++-+ I assume you meant that the above is vregistry? > > JOIN OF THE TWO > > ++---+-+ > |id | Name | Desc | > +---++-+ > | 1| Peter| A | > | 2| John | B | > | 3| Joe | | > | 4| Jerry| | > | 5| Mike | D | > ++---+-+ Unfortunately, PostgreSQL as of version 7.0 does not yet have left outer join. The traditional way to simulate this behavior is as follows: SELECT vcompany.id, vcompany.name, vregistry.desc FROM vcompany, vregistry WHERE vcompany.id = vregistry.id UNION SELECT vcompany.id, vcompany.name, NULL FROM vcompany WHERE NOT EXISTS (SELECT vregistry.id WHERE vregistry.id = vcompany.id) ORDER BY vcompany.id; Hope that helps, Mike Mascari
[GENERAL] LEFT OUTER JOIN?
Hi- I'm a newbie at postgres but have a pretty good understanding of SQL statements. I have created two views and wanted to do a LEFT OUTER JOIN of the two tables. The sytax is select * from vcompany LEFT OUTER JOIN ON vcompany.id=vregistry.id; I get the following error: LEFT OUTER JOIN not yet implemented My question is does postgresql 6.5 support Left out join and if not is there another way of implementing this sql statement to give me a LEFT OUTER JOIN. The logic if very simple. Look below to see the tables: vcompany ++---+ |id | Name | +---++ | 1 | Peter | | 2 | John | | 3 | Joe | | 4 | Jerry | | 5 | Mike | ++---+ vcompany ++-+ |id | Desc| +---+--+ | 1 | A | | 2 | B | | 5 | D | ++-+ JOIN OF THE TWO ++---+-+ |id | Name | Desc | +---++-+ | 1| Peter| A | | 2| John | B | | 3| Joe | | | 4| Jerry| | | 5| Mike | D | ++---+-+ __ Do You Yahoo!? Kick off your party with Yahoo! Invites. http://invites.yahoo.com/
[GENERAL] PG 7.0 is 2.5 times slower running a big report
I have a large report that I run once a day. Under 6.5.3 it took just over 3hrs to run. Under 7.0 it is now taking 8 hours to run. No other changes were made. This is on RedHat Linux 6.2. A PIII 733 with 384MB Ram, and 2 IDE 7200 RPM disks. One disk contains the Postgres directroy including the data directory, and the other disk has everything else. The Postmaster is started with these options: -i -B 1024 -N 256 -o -F The report is being run on a backup server just after the database has been loaded from a dump and 'vacuum analyse'd. There is practically nothing else running on the box during the run. The report creates four separate concurrent cursors. Each of the queries is sorted by a customer number which is an index. The index is unique in the customer table but not the others. For the other cursors it pops values as needed to process data for the current customer number. There are also other selects that are run for each customer order processed to retrieve its line items. The report does not update the database at all, it is just accumulating totals that will be written out when the report finishes. Top tells me the front end process is using 5 to 10 percent of the CPU and the back end is using 10 to 20 percent. The load average is about 1.0 and the CPU is about 80% idle. I am prettry certain on 6.5.3 that the CPU usage was much higher. Its almost like the new version has some sort of throttle to keep one backend from saturating the system. Indeed the box is much more responsive than it used to be while running this report. Suggestions?
Re: [GENERAL] Postgres Instability
On Wed, 24 May 2000, The Hermit Hacker wrote:
> On Wed, 24 May 2000, planx plnetx wrote:
>
> > I've remarked that postgresql-7.0 have two important bugs no, no...
> > I'll define its Problems.
> >
> > The first is that it is very subsceptible to ipc: it's true that sometimes
> > U need to do an ipcclean to remake it start
>
> This sounds like a Linux problem to me ... I've got v7.0 running on a
> server over here that is dealign with the Search engine for the PostgreSQL
> site (over 10million tuples in one table, indexing over 90k URLs) and the
> server has been running flawlessly for the past ~14days now, and what is
> only because that was the last time we rebooted it ...
Ditto, except the uptime here (PostgreSQL running all the time taking
care of user's mailboxes, web server, etc.): 265 days, 20:43 running
FreeBSD-3.2-Rel.
Vince.
>
>
>
> >
> > The second is the very VERY important instability problem.
> > I explain me better: I've tried Postgres on different systems
> > (redhat6.0,6.1, mandrake7.0) and every time after that I make start the
> > postmaster 20 or 30 times in a week or 2, it begin to have a problem
> > with shared memory, but i do an ipcclean and go..., after when
> > postmaster run without problems if I do a createuser (from sqladmin) or
> > a createdb (from user) it give me the same problem like:
> >
> >
> > FATAL 1: cannot create init file
> > mydatabasedirectory//base/mydb/pg_internal.init
> >
> >
> > it happens after few times of correct running... and persist also if I
> > recompile again postgresql
> >
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
>
>
--
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directoryhttp://www.camping-usa.com
Online Giftshop Superstorehttp://www.cloudninegifts.com
==
Re: [GENERAL] Postgres Instability
On Wed, 24 May 2000, planx plnetx wrote:
> I've remarked that postgresql-7.0 have two important bugs no, no...
> I'll define its Problems.
>
> The first is that it is very subsceptible to ipc: it's true that sometimes
> U need to do an ipcclean to remake it start
This sounds like a Linux problem to me ... I've got v7.0 running on a
server over here that is dealign with the Search engine for the PostgreSQL
site (over 10million tuples in one table, indexing over 90k URLs) and the
server has been running flawlessly for the past ~14days now, and what is
only because that was the last time we rebooted it ...
>
> The second is the very VERY important instability problem.
> I explain me better: I've tried Postgres on different systems
> (redhat6.0,6.1, mandrake7.0) and every time after that I make start the
> postmaster 20 or 30 times in a week or 2, it begin to have a problem
> with shared memory, but i do an ipcclean and go..., after when
> postmaster run without problems if I do a createuser (from sqladmin) or
> a createdb (from user) it give me the same problem like:
>
>
> FATAL 1: cannot create init file
> mydatabasedirectory//base/mydb/pg_internal.init
>
>
> it happens after few times of correct running... and persist also if I
> recompile again postgresql
>
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Migrating from mysql.
On Wed, 24 May 2000 14:26:32 -0500 "Ross J. Reedstrom" wrote: > Actually, it's "\d tablename". The rest is right, though. Teach me to try to tidy things up before posting won't it? Thanks! Giles (sigh, time for coffee)
Re: [GENERAL] Migrating from mysql.
Joe Karthauser wrote:
>
> Hi there,
>
> I'm migrating from MySQL to PostgreSQL and I was wondering whether someone
> could help me match some datatypes.
I'm trying to learn PostgreSQL myself. They say there's no better way
to learn than to teach, so here goes...
> Firstly MySQL has a 'timestamp' datatype which automatically updates with
> the current timestamp whenever an instance containing it is inserted or
> updated. Is there an equivalent datatype in PostgreSQL?
No. Try a combination of default value and an update rule. I've
included an example below. There was a discussion on this list recently
about when to use rules vs. triggers. You might want to read the
archives about that. Something I need to review more myself.
> Secondly MySQL supports an 'enum' datatype which allowed a number of
> labels to be defined as valid datatypes for a column, i.e:
>
> I can't seem to find the equivalent in PostgreSQL. Is there a way of doing
> this?
>
Yes. Use a CHECK constraint. I included one in the following example.
CREATE SEQUENCE mucho_mas_id_seq;
CREATE TABLE mucho_mas (
nameTEXT CHECK( name IN ('Larry','Billy')),
worth NUMERIC(14,2)
NOT NULL
DEFAULT '0',
updated TIMESTAMP
DEFAULT CURRENT_TIMESTAMP,
id INTEGER
DEFAULT NEXTVAL('mucho_mas_id_seq')
PRIMARY KEY
);
INSERT INTO mucho_mas (name, worth)
VALUES ('Larry','400.00');
INSERT INTO mucho_mas (name, worth)
VALUES ('Billy','400.01');
-- no mucho mas for me.
--
INSERT INTO mucho_mas (name, worth)
VALUES ('Ron','2.03');
SELECT *
FROM mucho_mas;
-- we need to updates on a view, rather than on table itself, because
-- if we update the table directly, we will have a circular rule
-- combination
--
CREATE VIEW mucho_mas_view AS
SELECT * FROM mucho_mas;
CREATE RULE mucho_mas_view_update AS
ON UPDATE TO mucho_mas_view
DO INSTEAD
UPDATE mucho_mas
SET worth = new.worth, updated = CURRENT_TIMESTAMP
WHERE id = old.id;
-- in real life, you'd probably do something more sophisticated to
-- select proper id value, but that's another problem.
--
UPDATE mucho_mas_view
SET worth = '400.02'
WHERE id = 1;
-- Hmm, must have been an accounting mistake. Let's fix that.
--
UPDATE mucho_mas_view
SET worth = '400.03'
WHERE id = 2;
SELECT *
FROM mucho_mas;
DROP VIEW mucho_mas_view;
DROP TABLE mucho_mas;
DROP SEQUENCE mucho_mas_id_seq;
> And last but not least I'm used to using the 'desc tablename' sql command
> to show the structure of a table within MySQL. How do I do the same in
> PostgreSQL.
I noticed someone already responded to this, so I won't repeat.
Good luck! I hope I haven't led you too far astray!
-Ron-
Re: [GENERAL] Migrating from mysql.
On Thu, May 25, 2000 at 04:58:48AM +1000, Giles Lean wrote: > > On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: > > > And last but not least I'm used to using the 'desc tablename' sql command > > to show the structure of a table within MySQL. How do I do the same in > > PostgreSQL. > > In psql "\i tablename". Check out \? or the documentation for all the > different backslash commands. You might want \z for access > permissions as well. Actually, it's "\d tablename". The rest is right, though. Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Migrating from mysql.
On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: > And last but not least I'm used to using the 'desc tablename' sql command > to show the structure of a table within MySQL. How do I do the same in > PostgreSQL. In psql "\i tablename". Check out \? or the documentation for all the different backslash commands. You might want \z for access permissions as well. Regards, Giles
[GENERAL] Postgres Instability
I've remarked that postgresql-7.0 have two important bugs no, no... I'll define its Problems. The first is that it is very subsceptible to ipc: it's true that sometimes U need to do an ipcclean to remake it start The second is the very VERY important instability problem. I explain me better: I've tried Postgres on different systems (redhat6.0,6.1, mandrake7.0) and every time after that I make start the postmaster 20 or 30 times in a week or 2, it begin to have a problem with shared memory, but i do an ipcclean and go..., after when postmaster run without problems if I do a createuser (from sqladmin) or a createdb (from user) it give me the same problem like: FATAL 1: cannot create init file mydatabasedirectory//base/mydb/pg_internal.init it happens after few times of correct running... and persist also if I recompile again postgresql Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Re: [GENERAL]
Uh, I cut & pasted the transcript in two pieces to get the selects in the same order, and messed up. The error happens _after_ connecting as anonymous, not before. Ross On Wed, May 24, 2000 at 01:09:58PM -0500, Ross J. Reedstrom wrote: > > idas=> select count(*) from urls; > ERROR: urls: Permission denied. > idas=> \c - anonymous > connecting as new user: anonymous > idas=> select count(*) from urls_p; > count > - >23 > (1 row) > > idas=>
RE: [GENERAL]
First of all, thank you very much for this detailed answer. (and also thanx
to Janet for asking :-] ) I am a postgresql newbie as well, and this is
very very helpful. I had been trying to get things to work under NT, but
decided to reboot to linux and try it there when I saw these detailed
instructions.
Fantastic stuff. Thank you so much Mr. Easter.
~
~ Maybe try getting postgresql-7.0.tar.gz. from www.postgresql.org. Then do
~ something like the following commands:
~
~ su -
~ groupadd postgres
~ adduser postgres
I found that on RedHat 6.1, I was only able to add a user or a group if I
was actually logged in as root. "su" didn't do it for me.
~ Note: make postgres user with default group postgres
~ cd /usr/local/src
~ tar -xvzf postgresql-7.0.tar.gz
~ cd postgresql-7.0
~ less INSTALL
cd /src
~ ./configure
~ --prefix=/usr/local/pgsql
~ --with-perl --with-tcl
~ --with-maxbackends=256
~ make
~ make install
cd ..
~ cd doc
~ make install
~ cd /usr/local
~ chown -R postgres:postgres pgsql
~ cd /etc
~ vi profile
~ Note: add /usr/local/pgsql/bin to PATH
~ add /usr/local/pgsql/man to MANPATH
~ set PGLIB=/usr/local/pgsql/lib
~ set PGDATA=/usr/local/pgsql/data
~ Then exit and log back in to take effect.
I found that this affected "my" environment variables, but it didn't affect
the environment variables for the postgres user i had created, so i added
these statements to the /home/postgres/.bashrc file. Is this good enough?
Or is there somewhere else that i should set these vars instead?
~ su - postgres
~ initdb
I got an error message here about needing to set PGDATA or use the --pgdata
switch (unfortunately i didn't paste the error message onto a floppy like i
thought i could so i could read it here in NT) -- i did make sure i had
created an empty data directory though
~ exit
~
~ Now you can start the database.
And that's as far as I got. I'll start it as soon as I'm sure I've got
everything right.
I'm presently trying the same (well,... similar) instructions on NT. The
obvious question is what to do about creating a user. On NT it just isn't
possible to su postgres
initdb
exit
So does that mean i just run it as myself? This is in a non-production
environment on my workstation, where I'm hoping to eventually be addressing
the database w/zope.
Again, I can't thank this list enough for all the great info i've picked up
while lurking,
~c
~ To start it, you can use pg_ctl
~ that comes
~ with postgresql, or you can make a script like the following
~ /etc/rc.d/init.d/postgresql:
~
~ #!/bin/sh
~ # See how we were called.
~ case "$1" in
~ start)
~ # Start daemons.
~ echo -n "Starting postgres Postmaster daemon:"
~ if [ -z "`pidof -s postmaster`" ]
~ then
~ su postgres -c "/usr/local/pgsql/bin/postmaster
~ -S -D /usr/local/pgsql/data &"
~ echo -n " postmaster"
~ else
~ echo -n " (already running)"
~ fi
~ echo
~ #touch /var/lock/subsys/postgres
~ ;;
~ stop)
~ # Stop daemons.
~ echo -n "Shutting down postgres Postmaster daemon: "
~ killall -TERM postmaster 2>/dev/null
~ killall -TERM postgres 2>/dev/null
~ echo postmaster postgres
~ rm -f /tmp/.s.PGSQL.5432
~ ;;
~ *)
~ echo "Usage: postgres {start|stop}"
~ exit 1
~ esac
~ exit 0
~
~ Now start the database:
~ /etc/rc.d/init.d/postgresql start
~ You will have to add this into your startup scripts.
~ /etc/rc.d/init.d/postgresql stop, can be put in your shutdown scripts.
~
~ Then do the following with the server started:
~
~ su - postgres
~ createuser
~ Do this for each user you will have.
~
~ Once you have your user created, you can login to the user and run:
~
~ createdb
~ Note: creates a database called by default.
~ psql
~ Note: connect to your database by default.
~
~ At this point you'll be able to start using SQL and create tables etc.
~ I think these instructions are ok. Hope it helps.
~
sorry for the waste of bandwidth but that stuff is just too valuable to snip
:-]
Re: [GENERAL]
On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote: > One problem you may have with this is that if a function accesses some > table, the user who uses that function must also have permissions on the > table. I have a similar problem. I'd like to give permissions on a view, > but not on the table underlying the view (the view serves to filter out > some records the user shouldn't see). I can't give permission to use view > without giving permission to use the table. Have you tried it? This is one of the things views are for. The view accesses it's underlying tables as the user who created the view, as far as I recall. I, for example, have an entire database where every table has a 'pub' boolean. I've created views that return only rows with pub = 't', and given the anonymous user (which the web server connect as) select privileges only on the view. idas=> select count(*) from urls; count - 23 (1 row) idas=> select count(*) from urls_p; count - 23 (1 row) idas=> select count(*) from urls; ERROR: urls: Permission denied. idas=> \c - anonymous connecting as new user: anonymous idas=> select count(*) from urls_p; count - 23 (1 row) idas=> Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] 7.0 installation problem, help please :-(
That's odd. This is the error I got compiling pgsql 6.5 on Solaris. I never resolved the problem. However, the 7.0 source did not give this error. Maybe this is a stupid question, but are you sure you have the most recent source code? Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer On Tue, 23 May 2000, Chris Chan wrote: > > I'm pgsql newbie. I try to install the pgsql 7.0 on my Solaris 8 x86 server > but fail. The following is the error message: > > > stringinfo.c: In function `appendStringInfo': > stringinfo.c:104: `va_list' undeclared (first use in this function) > stringinfo.c:104: (Each undeclared identifier is reported only once > stringinfo.c:104: for each function it appears in.) > stringinfo.c:104: parse error before `args' > stringinfo.c:121: warning: implicit declaration of function `va_start' > stringinfo.c:121: `args' undeclared (first use in this function) > stringinfo.c:124: warning: implicit declaration of function `va_end' > gmake[2]: *** [stringinfo.o] Error 1 > gmake[2]: Leaving directory `/usr/share/src/postgresql-7.0/src/backend/lib' > gmake[1]: *** [lib.dir] Error 2 > gmake[1]: Leaving directory `/usr/share/src/postgresql-7.0/src/backend' > gmake: *** [all] Error 2
[GENERAL] Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
Hello everyone! :) This little problem is bothering me a lot! It seems that PostgreSQL 7.0 uses different semantics than Oracle when evaluting SQL?! I have two relations, A and B, both containing the attributes "number" (int) and "amount" (int). There's no primary key, and the two relations can contain multiple identical tuples. I would like to query for a table containing the total amount for each different number in B, substracted from the total amount for each different number in A. In other words, sum A by grouping "number", sum B by grouping "number" and finaly calculate the difference between the sums for each "number". I have defined two views, viewA and viewB. They are defined as follow: CREATE VIEW viewA AS SELECT number, sum(amount) AS amount FROM A GROUP BY number; CREATE VIEW viewB AS SELECT number, sum(amount) AS amount FROM B FROUP BY number; This query then gives me the desired result (when I'm using Oracle): SELECT viewA.number, viewA.amount - viewB.amount AS difference FROM viewA, viewB WHERE viewA.number = viewB.number BUT WHEN I'm doing the EXACT SAME THING in PostgreSQL 7.0 I get a different result! It seems that Postgres executes the natural join in the query BEFORE performing the sum() in the definition of the views thus giving me a wrong result. How can I fix that?? How come PostgreSQL uses different semantics when evaluating SQL expressions than other BDMSs? Thank you! :)) Mvh. Thomas Holmgren Institut for Datalogi Aalborg Universitet
Re: [GENERAL] Migrating from mysql.
> Hi there, > > I'm migrating from MySQL to PostgreSQL and I was wondering whether someone > could help me match some datatypes. I'm also migrating, and would appreciate any thoughts on how to do it. Spesifically, if there could be any problems with functions not supported on the PostgreSQL and such. Pointers to documents, HOWTOs etc. are much appreciated. -Morten
