Re: [GENERAL] Question about load balance
On 2012-06-11 22:47, John R Pierce wrote: On 06/11/12 12:17 PM, Condor wrote: May be because some times when some one start a new business does not have 20k $ for a new server and resource of the server is enough for the moment and as I planed is enough for this year. and when you start a new business, you don't lease a campus large enough for 10,000 employees, you deal with that when you need it. if your app actually ends up needing to scale to google size, plan on having to redesign it a few times. -- john r pierceN 37, W 122 santa cruz ca mid-left coast --- cut --- My question was how stable is pgpool, what problems I can expect, and pure curiosity what is the technique for managing large databases. CPU and memory to the second coming or are there other techniques for scattering applications on other servers. --- cut --- I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. H. -- 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] Question about load balance
My question was how stable is pgpool, what problems I can expect, and pure curiosity what is the technique for managing large databases. CPU and memory to the second coming or are there other techniques for scattering applications on other servers. --- cut --- I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. You'd better to subscribe pgpool-gene...@pgpool.net list (http://www.pgpool.net/mediawiki/index.php/Mailing_lists) and post this kind of question. There are many people who are serious about pgpool in the list. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Question about load balance
Hi, I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. In this case it starts to get very specific about what you are trying to accomplish. Transactional databases offer a lot guarantees, which makes it hard to just add another machine to the cluster, this isn't a webserver ;) - Clemens -- 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] Postgres 9.2 beta2 one-click installer on windows
[CCed the list] On Tue, Jun 12, 2012 at 12:33 PM, Sachin Srivastava sachin.srivast...@enterprisedb.com wrote: On Tue, Jun 12, 2012 at 12:31 PM, Craig Ringer ring...@ringerc.id.auwrote: On 06/11/2012 08:36 PM, Marc Watson wrote: *De :*Craig Ringer [mailto:ring...@ringerc.id.au] Is there any chance you can get the version of the currently installed MSVC++ redistributible(s) on your computer? They're listed in programs and features in the control panel. I originally had version 10.0.40219 installed in both 32 and 64 bit, which gave me the error. I uninstalled these versions just to get around the 9.2 beta2 install, which installed 10.0.30319.01. OK, so that confirms it. The Pg installer needs to have an updated redist bundled when the final version is released, and needs to treate error 5100 as success. I have updated the redist and modified the installer to not treat 5100 as error (we already handle 3010 return code properly). I am in the process of testing the changes. -- Craig Ringer -- Regards, Sachin Srivastava EnterpriseDB, India -- Regards, Sachin Srivastava EnterpriseDB, India
Re: [GENERAL] Segmentation Fault
On 06/12/2012 03:15 AM, Benson Jin wrote: Hi All, A silly question how do I get install external symbols for postgresql, if compiled it myself previously? Do I recompile it with --enable-debug option? If you didn't strip the executable then the cores produced even without --enable-debug will be somewhat useful, but debug info is ideal. You could try that first, since it'll give you a chance to make sure you can at least get and analyse a core file before spending time recompiling and reinstalling Pg. Recompiling with --enable-debug is the easy way to get executables with debug info embedded in them. If the installed libraries, etc haven't changed since compiling your original copy of PostgreSQL, and if you're compiling the EXACT same source code, you can debug the core against these executables without replacing the ones you're actually running. Something like a libc upgrade since the original copies were built might mean that the new debug executables are no longer exactly compatible with the ones your core file came from, though, so the results can't be totally trusted. Ideally you want to run the debug executables and get the core to debug from them. An --enable-debug build on gcc will still be optimised and should have no detectable performance difference. The executables are huge, but the ELF sections containing the debug info never get mapped in, so it doesn't actually matter. An alternative is to build with --enable-debug, strip the debug info into external symbols packages using strip --only-keep-debug. There isn't much point unless disk space consumed by executables is a big concern, though. I always use --enable-debug when building Pg. I rarely need the symbols, but it's handy to have them when I do. -- Craig Ringer
Re: [GENERAL] Question about load balance
On 06/11/12 11:29 PM, Condor wrote: I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. you handle large databases with a lot of fast disk, and memory, this gets you into the terabytes. clustering/load balancing would not do for this, other than needing MORE fast disk (N replicas require N times the disk system of one database). clustering can provide active/slave failover for high availability, or it can provide replicas for balancing read queries. updates have to be made to all the replicas, so they wont be any faster than a single server (in fact, will be slower due to the overhead of replication -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem installing extensions on Lion
Hi there, I upgraded my machine from Snow Leopard to Lion, and ran, as usual, into some Postgres update problems. I used Kyngchaos libraries for all frameworks and Postgres. Now, I'd like to install the tablefunc/crosstab function. But the make process gives me this: cd contrib/tablefunc tablefunc $ make gcc -Os -arch x86_64 -isysroot /Developer/SDKs/MacOSX10.6.sdk -D_FILE_OFFSET_BITS=64 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -I. -I. -I/usr/local/pgsql-9.1/include/server -I/usr/local/pgsql-9.1/include/internal -I/usr/include/libxml2 -c -o tablefunc.o tablefunc.c In file included from /usr/local/pgsql-9.1/include/server/postgres.h:47, from tablefunc.c:33: /usr/local/pgsql-9.1/include/server/c.h:67:19: error: stdio.h: No such file or directory /usr/local/pgsql-9.1/include/server/c.h:68:20: error: stdlib.h: No such file or directory /usr/local/pgsql-9.1/include/server/c.h:69:20: error: string.h: No such file or directory I have a similar issue with the compilation of the Postgis lib. Thanks a lot for any tip! Stefan
Re: [GENERAL] import *.backup-file (PostGIS - not mine)
There are few errors after my pg_restore command. pg_restore -i -h localhost -p 5432 -U postgres -d THS -v H:.../netzknotenmodell_etrs89.backup could not execute query: ERROR: type public.geometry is only a shell could not execute query: ERROR: relation strasse.netzknotenmodell_etrs89 does not exist could not execute query: ERROR: relation netzknotenmodell_etrs89 does not exist could not execute query: ERROR: relation netzknotenmodell_etrs89 does not exist could not execute query: ERROR: relation netzknotenmodell_etrs89 does not exist pg_restore -i -h localhost -p 5432 -U postgres -d THS -v H:.../fahrbahnflaeche_etrs89.backup could not execute query: ERROR: type public.geometry is only a shell could not execute query: ERROR: relation strasse.fahrbahnflaeche_etrs89 does not exist could not execute query: ERROR: relation fahrbahnflaeche_etrs89 does not exist could not execute query: ERROR: relation fahrbahnflaeche_etrs89 does not exist could not execute query: ERROR: relation fahrbahnflaeche_etrs89 does not exist pg_restore -i -h localhost -p 5432 -U postgres -d THS -v H:.../strassenabschnitte_etrs89.backup could not execute query: ERROR: type public.geometry is only a shell could not execute query: ERROR: relation strasse.strassenabschnitte_etrs89 does not exist could not execute query: ERROR: relation strassenabschnitte_etrs89 does not exist could not execute query: ERROR: relation strassenabschnitte_etrs89 does not exist could not execute query: ERROR: relation strassenabschnitte_etrs89 does not exist pg_restore -i -h localhost -p 5432 -U postgres -d THS -v H:.../strassennetzmodell_etrs89.backup could not execute query: ERROR: type public.geometry is only a shell could not execute query: ERROR: relation strasse.strassennetzmodell_etrs89 does not exist could not execute query: ERROR: relation strassennetzmodell_etrs89 does not exist could not execute query: ERROR: relation strassennetzmodell_etrs89 does not exist could not execute query: ERROR: relation strassennetzmodell_etrs89 does not exist I got the same errors when I used the -Fc. Do you with your experience know whats's to do with these 4 backup-files or which command is to use? best regards Thomas -- View this message in context: http://postgresql.1045698.n5.nabble.com/import-backup-file-PostGIS-tp5712030p5712158.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] import *.backup-file (PostGIS - not mine)
gipsy-king1 stue...@gis-consult.de writes: There are few errors after my pg_restore command. pg_restore -i -h localhost -p 5432 -U postgres -d THS -v H:.../netzknotenmodell_etrs89.backup could not execute query: ERROR: type public.geometry is only a shell Are these partial dumps? It appears that you don't have postgis installed in the target database, but a full dump should have included the postgis types and functions. If they were partial, what you need to do is install postgis first. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function already exists with same argument types
Dear all I want to import a postGIS-DB backup-file. When I execute this command: *pg_restore -i -h localhost -p 5432 -U postgres -d THS -v path/alkis.backup path2\output.txt 21* I get an textfile with all outputs. There are lots of ERRORs like this: *pg_restore: erstelle FUNCTION st_geometry_analyze(internal) pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 33; 1255 121878509 FUNCTION st_geometry_analyze(internal) postgres pg_restore: [Archivierer (DB)] could not execute query: ERROR: function st_geometry_analyze already exists with same argument types Command was: CREATE FUNCTION st_geometry_analyze(internal) RETURNS boolean AS '$libdir/liblwgeom', 'LWGEOM_analyze' LANGUAGE c ST...* /I heard that the restore-command will apply all functions a second time. Is there a possibility to disable this inside the pg_restore-command? I don't want to delete all function by hand inside the pdAdmin before I'll execute the pg_restore-command./ best regards, Thomas -- View this message in context: http://postgresql.1045698.n5.nabble.com/Function-already-exists-with-same-argument-types-tp5712191.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to create c language in postgresql database. Thanks.
I need to create c language in the postgresql database. I only know to create as follows. Create language c ; Is there anything else I need to do before I create the C language? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovery continually requests new WAL files
Hey!
I have a simple setup with one master and one backup server. I have an
issue where I have performed a backup and copied it to the data
directory for the slave, written a recovery.conf and copied in the
backup_label file and then started the server, it happily restores
everything up until and including the WAL file mentioned in the
backup_label and then attempts to obtain the next archive file which has
not yet been archived. I can't for the life of me figure out what is
going on.
Here's a break down of what I do
call pg_start_backup('label')
tar -zcf backup.tar.gz base global pg_clog pg_multixact pg_notify
pg_serial pg_subtrans pg_tblspc pg_twophase backup_label
call pg_stop_backup()
scp pgsql.tar.gz slave_hostname:/var/lib/postgresql/9.1/main
move to slave server
rm -rf global base pg_clog pg_multixact pg_notify pg_serial pg_subtrans
pg_tblspc pg_twophase pg_xlog/*
mkdir pg_xlog/archive_status
tar -xvf backup.tar.gz
restart postgresql
recovery.conf
-
restore_command = 'scp
master-hostname:/var/lib/postgresql/9.1/main/wal_archives/%f %p'
standby_mode=on
And here's what I'm seeing in the logs on the recovering server
2012-06-12 16:31:26 UTC FATAL: the database system is starting up
2012-06-12 16:31:27 UTC FATAL: the database system is starting up
2012-06-12 16:31:27 UTC FATAL: the database system is starting up
2012-06-12 16:31:27 UTC LOG: incomplete startup packet
2012-06-12 16:31:30 UTC LOG: restored log file
0001000A from archive
2012-06-12 16:31:30 UTC LOG: redo starts at 0/A78
2012-06-12 16:31:30 UTC LOG: consistent recovery state reached at 0/B00
scp: /var/lib/postgresql/9.1/main/wal_archives/0001000B:
No such file or directory
scp: /var/lib/postgresql/9.1/main/wal_archives/0001000B:
No such file or directory
scp: /var/lib/postgresql/9.1/main/wal_archives/0001000B:
No such file or directory
I'm confused by this because the 0001000B archive wasn't
created until after the pg_stop_backup call so why is it needed?
Any help would be appreciated, I've been banging my head against this
one for a while.
Thanks
Alex
--
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] How to create c language in postgresql database. Thanks.
On 12/06/2012 17:53, leaf_yxj wrote: I need to create c language in the postgresql database. I only know to create as follows. Create language c ; Is there anything else I need to do before I create the C language? Hello Grace, C isn't available (AFAIK) as a procedural language; however, you have a wide choice of other languages to choose from - see the docs under Server programming for some of them. In fact, C is the language used to write PostgreSQL. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to declare return type for a function returning several rows and columns?
Hello,
I'm trying to create the following function which gives me
a runtime error, because it obviously doesn't return a mere
integer but several rows and columns (result of a join):
# create or replace function pref_daily_misere() returns setof integer as $BODY$
begin
create temporary table temp_ids (id varchar not null) on
commit drop;
insert into temp_ids (id)
select id
from pref_money
where yw = to_char(current_timestamp - interval '1
week', 'IYYY-IW')
order by money
desc limit 10;
create temporary table temp_rids (rid integer not null) on
commit drop;
insert into temp_rids (rid)
select rid
from pref_cards
where id in (select id from temp_ids) and
bid = 'Мизер' and
trix 0;
-- return query select rid from temp_rids;
return query SELECT r.rid, r.cards, to_char(r.stamp,
'DD.MM. HH24:MI') as day,
c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit,
u.id, u.first_name, u.avatar, u.female, u.city,
u.vip CURRENT_DATE as vip
FROM pref_rounds r
JOIN pref_cards c1 USING (rid)
JOIN pref_cards c2 USING (rid)
JOIN pref_users u ON u.id = c2.id
WHERE r.rid in (select rid from temp_rids) order
by rid, pos;
return;
end;
$BODY$ language plpgsql;
The runtime error in PostgreSQL 8.4.11 is:
# select pref_daily_misere();
ERROR: structure of query does not match function result type
DETAIL: Number of returned columns (15) does not match expected
column count (1).
CONTEXT: PL/pgSQL function pref_daily_misere line 18 at RETURN QUERY
Does anybody please have an advice here?
The background: I have a table holding card game rounds:
# \d pref_rounds;
Table public.pref_rounds
Column |Type | Modifiers
+-+---
rid| integer | not null default
nextval('pref_rounds_rid_seq'::regclass)
cards | text|
stamp | timestamp without time zone | default now()
Indexes:
pref_rounds_pkey PRIMARY KEY, btree (rid)
Referenced by:
TABLE pref_cards CONSTRAINT pref_cards_rid_fkey FOREIGN KEY
(rid) REFERENCES pref_rounds(rid) ON DELETE CASCADE
Each round is played by 3 players:
# \d pref_cards;
Table public.pref_cards
Column |Type | Modifiers
-+-+---
rid | integer |
id | character varying(32) |
bid | character varying(32) | not null
trix| integer | not null
pos | integer | not null
money | integer | not null
last_ip | inet|
quit| boolean |
stamp | timestamp without time zone | default now()
Indexes:
pref_cards_id_index btree (id)
pref_cards_rid_index btree (rid)
Foreign-key constraints:
pref_cards_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE
pref_cards_rid_fkey FOREIGN KEY (rid) REFERENCES
pref_rounds(rid) ON DELETE CASCADE
I'm trying to take the top 10 players of the last week,
so that I can display their certain bids for analyse on
a web page (similar to the table in the middle of
http://preferans.de/user.php?id=OK471018960997 )
Thank you
Alex
--
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] How to create c language in postgresql database. Thanks.
leaf_yxj leaf_...@163.com writes: I need to create c language in the postgresql database. Why do you think you need to do that? It's built-in. regards, tom lane -- 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] How to declare return type for a function returning several rows and columns?
Hey Alexander, 2012/6/12 Alexander Farber alexander.far...@gmail.com Hello, I'm trying to create the following function which gives me a runtime error, because it obviously doesn't return a mere integer but several rows and columns (result of a join): # create or replace function pref_daily_misere() returns setof integer as $BODY$ begin create temporary table temp_ids (id varchar not null) on commit drop; insert into temp_ids (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; create temporary table temp_rids (rid integer not null) on commit drop; insert into temp_rids (rid) select rid from pref_cards where id in (select id from temp_ids) and bid = 'Мизер' and trix 0; -- return query select rid from temp_rids; return query SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r JOIN pref_cards c1 USING (rid) JOIN pref_cards c2 USING (rid) JOIN pref_users u ON u.id = c2.id WHERE r.rid in (select rid from temp_rids) order by rid, pos; return; end; $BODY$ language plpgsql; The runtime error in PostgreSQL 8.4.11 is: # select pref_daily_misere(); ERROR: structure of query does not match function result type DETAIL: Number of returned columns (15) does not match expected column count (1). CONTEXT: PL/pgSQL function pref_daily_misere line 18 at RETURN QUERY Does anybody please have an advice here? You can create the view with your query: SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r JOIN pref_cards c1 USING (rid) JOIN pref_cards c2 USING (rid) JOIN pref_users u ON u.id = c2.id; and use this view both as the function return type and for selecting inside the function. -- // Dmitriy.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
On 06/12/12 9:53 AM, leaf_yxj wrote: I need to create c language in the postgresql database. I only know to create as follows. Create language c ; to clarify what tom said, you externally compile your C functions into .so/.dll files, then bind them in with CREATE FUNCTION name(args) RETURNS . AS 'path/filename' LANGUAGE C see http://www.postgresql.org/docs/current/static/xfunc-c.html -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] How to create c language in postgresql database. Thanks.
Hi Tom, Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? Thanks. Regards. Grace At 2012-06-13 01:55:52,Tom Lane t...@sss.pgh.pa.us wrote: leaf_yxj leaf_...@163.com writes: I need to create c language in the postgresql database. Why do you think you need to do that? It's built-in. regards, tom lane
Re: [GENERAL] How to create c language in postgresql database. Thanks.
Hi Raymond, Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? Thanks. Regards. Grace At 2012-06-13 01:35:49,Raymond O'Donnell [via PostgreSQL] ml-node+s1045698n5712228...@n5.nabble.com wrote: On 12/06/2012 17:53, leaf_yxj wrote: I need to create c language in the postgresql database. I only know to create as follows. Create language c ; Is there anything else I need to do before I create the C language? Hello Grace, C isn't available (AFAIK) as a procedural language; however, you have a wide choice of other languages to choose from - see the docs under Server programming for some of them. In fact, C is the language used to write PostgreSQL. Ray. -- Raymond O'Donnell :: Galway :: Ireland [hidden email] -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712228.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712240.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
Hi John, Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? Thanks. Regards. Grace At 2012-06-13 02:14:06,John R Pierce [via PostgreSQL] ml-node+s1045698n5712238...@n5.nabble.com wrote: On 06/12/12 9:53 AM, leaf_yxj wrote: I need to create c language in the postgresql database. I only know to create as follows. Create language c ; to clarify what tom said, you externally compile your C functions into .so/.dll files, then bind them in with CREATE FUNCTION name(args) RETURNS . AS 'path/filename' LANGUAGE C see http://www.postgresql.org/docs/current/static/xfunc-c.html -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712238.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712242.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
Hi Tom, Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712244.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] How to create c language in postgresql database. Thanks.
On 06/12/12 11:25 AM, leaf_yxj wrote: Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? yes, only a sql superuser can define a C function, as these have total access to crashing postgres's innards. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] How to create c language in postgresql database. Thanks.
John, So can I ( the superuser) do it by myself and how can I do it? Thanks. Grace At 2012-06-13 02:48:20,John R Pierce [via PostgreSQL] ml-node+s1045698n5712251...@n5.nabble.com wrote: On 06/12/12 11:25 AM, leaf_yxj wrote: Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? yes, only a sql superuser can define a C function, as these have total access to crashing postgres's innards. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712251.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712254.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
On 06/12/12 12:04 PM, leaf_yxj wrote: So can I ( the superuser) do it by myself and how can I do it? the devs would give you the .dll/.so file, and a .sql scrap to do the install. you'd put the .so/.dll in an appropriate place, and run the .sql scrap to install it into a given database. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] How to create c language in postgresql database. Thanks.
Hi John, one more question: so it's one time jobs or it need keep doing. Thanks. Grace At 2012-06-13 02:48:20,John R Pierce [via PostgreSQL] ml-node+s1045698n5712251...@n5.nabble.com wrote: On 06/12/12 11:25 AM, leaf_yxj wrote: Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? yes, only a sql superuser can define a C function, as these have total access to crashing postgres's innards. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712251.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712255.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
On 06/12/12 12:07 PM, leaf_yxj wrote: one more question: so it's one time jobs or it need keep doing. each time the C function is updated, you would need to DROP FUNCTION / replace binary / CREATE FUNCTION in any database that needs it. and each time you create a new database that needs it, you'd need to CREATE FUNCTION -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] How to create c language in postgresql database. Thanks.
Hi John, Thanks for your quick reply. It's really help me a lot. 1) What's the info in .dll/.so ? 2) .sql scrap is the binary installation file? 3) For the same database, we need update C functions frequently ? Thanks. Regards. Grace At 2012-06-13 03:08:26,John R Pierce [via PostgreSQL] ml-node+s1045698n5712256...@n5.nabble.com wrote: On 06/12/12 12:04 PM, leaf_yxj wrote: So can I ( the superuser) do it by myself and how can I do it? the devs would give you the .dll/.so file, and a .sql scrap to do the install. you'd put the .so/.dll in an appropriate place, and run the .sql scrap to install it into a given database. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712256.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712259.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] How to create c language in postgresql database. Thanks.
On 06/12/12 12:22 PM, leaf_yxj wrote: 1) What's the info in .dll/.so ? thats the binary code compiled and linked from C, windows calls this DLL (Dynamic Link Library), while unix usually calls it SO (Shared Object). Mac OSX has yet another name (dylib or something). 2) .sql scrap is the binary installation file? the .sql is the CREATE FUNCTION statement mostly. 3) For the same database, we need update C functions frequently ? you tell me? how often do your programmers change them? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] How to create c language in postgresql database. Thanks.
Hi John, Thanks for your useful info. I really apprecaite it. I got this problem when the SAS application try to install the scoring model. So I am very confused. 1) What's the info in .dll/.so ? thats the binary code compiled and linked from C, windows calls this DLL (Dynamic Link Library), while unix usually calls it SO (Shared Object). Mac OSX has yet another name (dylib or something). Grace comments : this need the superuser privileges on the server to copy those .so ( we are on the linux server) to the postgresql server ? correct 2) .sql scrap is the binary installation file? the .sql is the CREATE FUNCTION statement mostly. --- Grace comments : we already grant create privileges on the saslib schema. So it means they can this scrap by themselves ??? 3) For the same database, we need update C functions frequently ? you tell me? how often do your programmers change them? Grace commends : they tried to SAS to create lot of models. So I amn't sure about this issues. But John, thanks a lot for your answers. Thanks. Regards. Grace At 2012-06-13 03:27:45,John R Pierce [via PostgreSQL] ml-node+s1045698n5712261...@n5.nabble.com wrote: On 06/12/12 12:22 PM, leaf_yxj wrote: 1) What's the info in .dll/.so ? thats the binary code compiled and linked from C, windows calls this DLL (Dynamic Link Library), while unix usually calls it SO (Shared Object). Mac OSX has yet another name (dylib or something). 2) .sql scrap is the binary installation file? the .sql is the CREATE FUNCTION statement mostly. 3) For the same database, we need update C functions frequently ? you tell me? how often do your programmers change them? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712261.html To unsubscribe from How to create c language in postgresql database. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712264.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] trigger on view returning created serial
Hello, I have created a view and on this view a trigger, which is called on an insert command on the view. Within the trigger I run an insert on a table and one of the table fields uses a serial sequence, that creates values. If I run in the trigger after the insert a return NEW, the field (id) which should be set by the serial on the table is 0, so how can I get the new created serial on the insert command within the trigger function? Thanks Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reference with inheritance propagate data
Hello, All ! I have base table q_base_table with column (id bigint) which may be inherited by users tables, primary key for both base table and derived tables is id. Now I need for another table record_rubricator which has to be referenced to base and derived tables, which way I have to do it, because postgresql does not allow automatically propagate data ? -- Best regards, Sincerely yours, Yuriy Rusinov. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE Syntax Check
One table, waterchem, with primary key 'site' has columns easting and northing with no values in them. A second table, sites, with primary key 'name' has values for easting and northing for each row. I want to update waterchem with the easting and northing values from sites. My proposed statement is, UPDATE waterchem SET waterchem.easting, waterchem.northing = sites.easting, sites.northing FROM sites AS s WHERE waterchem.site = s.name; Is this correct? If not, what approach should I adopt? Rich -- 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] UPDATE Syntax Check
Rich Shepard wrote on 13.06.2012 00:17: One table, waterchem, with primary key 'site' has columns easting and northing with no values in them. A second table, sites, with primary key 'name' has values for easting and northing for each row. I want to update waterchem with the easting and northing values from sites. My proposed statement is, UPDATE waterchem SET waterchem.easting, waterchem.northing = sites.easting, sites.northing FROM sites AS s WHERE waterchem.site = s.name; Is this correct? If not, what approach should I adopt? No it's not quite correct: UPDATE waterchem SET waterchem.easting = s.easting waterchem.northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; -- 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] UPDATE Syntax Check
On Wed, 13 Jun 2012, Thomas Kellerer wrote: No it's not quite correct: UPDATE waterchem SET waterchem.easting = s.easting waterchem.northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; Thomas, I wondered about this syntax, and doing each column separately. Thanks for the correction, Rich -- 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] UPDATE Syntax Check
On Wed, 13 Jun 2012, Thomas Kellerer wrote: UPDATE waterchem SET waterchem.easting = s.easting waterchem.northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; Thomas, For the record, running this pushed me to the correct syntax: UPDATE waterchem SET easting = s.easting northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; The target does not want the table name repeated; postgres looks for a column named 'waterchem.easting'. Regards, Rich -- 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] Reference with inheritance propagate data
On Wed, 2012-06-13 at 00:38 +0400, Yuriy Rusinov wrote: Hello, All ! I have base table q_base_table with column (id bigint) which may be inherited by users tables, primary key for both base table and derived tables is id. Now I need for another table record_rubricator which has to be referenced to base and derived tables, which way I have to do it, because postgresql does not allow automatically propagate data ? One foreign key cannot reference two tables. Have you considered a design that does not use inheritance? For instance, the users table could reference q_base_table, and then record_rubricator could also reference q_base_table? Also, I don't understand what you mean about propagating data. What data do you want to propagate? Regards, Jeff Davis -- 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] trigger on view returning created serial
On Tue, 2012-06-12 at 22:35 +0200, Philipp Kraus wrote: Hello, I have created a view and on this view a trigger, which is called on an insert command on the view. Within the trigger I run an insert on a table and one of the table fields uses a serial sequence, that creates values. If I run in the trigger after the insert a return NEW, the field (id) which should be set by the serial on the table is 0, so how can I get the new created serial on the insert command within the trigger function? It's hard for me to tell exactly what problem you're describing, but it sounds similar to the one solved here: http://people.planetpostgresql.org/dfetter/index.php?/archives/66-VIEW-triggers-RETURNINGhtml If I misunderstood, please be more descriptive about what you are trying to do, what code you wrote, and what went wrong. Regards, Jeff Davis -- 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] UPDATE Syntax Check
On Jun 12, 2012, at 18:50, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 13 Jun 2012, Thomas Kellerer wrote: UPDATE waterchem SET waterchem.easting = s.easting waterchem.northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; Thomas, For the record, running this pushed me to the correct syntax: UPDATE waterchem SET easting = s.easting northing = s.northing FROM sites AS s WHERE waterchem.site = s.name; The target does not want the table name repeated; postgres looks for a column named 'waterchem.easting'. Regards, Rich You are right regarding the table name in the set portion, it has to be the specified table. However, you can alias the table so that instead of waterchem.site you could do something like wc.site And, to be thorough, you need to put commas between each field you want to update. easting = s.easting, northing = s.northing -- 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] Problem installing extensions on Lion
On 06/12/2012 08:21 PM, Stefan Schwarzer wrote: Hi there, I upgraded my machine from Snow Leopard to Lion, and ran, as usual, into some Postgres update problems. I used Kyngchaos libraries for all frameworks and Postgres. Now, I'd like to install the tablefunc/crosstab function. But the make process gives me this: Looks like your dev env is broken or incomplete. -- Craig Ringer
Re: [GENERAL] Function already exists with same argument types
On 06/12/2012 09:01 PM, gipsy-king1 wrote: Dear all I want to import a postGIS-DB backup-file. When I execute this command: *pg_restore -i -h localhost -p 5432 -U postgres -d THS -v path/alkis.backup path2\output.txt 21* I get an textfile with all outputs. There are lots of ERRORs like this: When you create the database to restore to, create it from template0. postgres# CREATE DATABASE THS WITH TEMPLATE template0; ... adding any LOCALE, ENCODING, OWNER, etc directives you need too. That'll ensure you're stating with an empty target to restore to. Alternately, you can use pg_restore with the -C option to tell it to make a new database to restore to. It'll create a new DB with the same name as the one you dumped, and restore to it. Strangely, you must tell pg_restore to connect to a different database in order to create a database, eg: pg_restore -C --dbname postgres -U postgres -h localhost -p 5432 -i -v path/alkis.backup will restore to a NEW database (not to the postgres database) created with the same name as the db that was dumped had. Note, however, that this WILL NOT WORK if you're restoring a dump made on Linux to a Windows box or vice versa due to a limitation/bug in how PostgreSQL and pg_restore handle locales and encodings. You must use the CREATE DATABASE followed by separate pg_restore method in that case. -- Craig Ringer -- 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] UPDATE Syntax Check
On Tue, 12 Jun 2012, David Johnston wrote: And, to be thorough, you need to put commas between each field you want to update. Did that. Didn't write it that way in the message. Regards, Rich -- 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] How to create c language in postgresql database. Thanks.
On 06/13/2012 03:35 AM, leaf_yxj wrote: Hi John, Thanks for your useful info. I really apprecaite it. I got this problem when the SAS application try to install the scoring model. So I am very confused. 1) What's the info in .dll/.so ? thats the binary code compiled and linked from C, windows calls this DLL (Dynamic Link Library), while unix usually calls it SO (Shared Object). Mac OSX has yet another name (dylib or something). Grace comments : this need the superuser privileges on the server to copy those .so ( we are on the linux server) to the postgresql server ? correct And to dynamically link them into the PostgreSQL executable to run them. Most importantly, you have to COMPLTELY AND UTTERLY TRUST YOUR DEV TEAM. If you let them install a C library they've written, you've given them total power over your database, so they can just use their C function to give themselves superuser privs if they want it. This isn't a bad thing; C functions are extremely fast and powerful tools in a database, you just have to trust the person or people who wrote them. A C library also has much greater opportunity to muck up your database. In particular it can crash database backends very easily. You need to know the people who wrote it are competent and tested it well in a non-production environment. You need to read about dlopen or LoadLibrary, about dynamic linking, about compilation and shared libraries in C, etc. Right now you don't have the background in how this works to make appropriate decisions; you have to either trust your dev team to do the right thing, or tell them to do what they need to without using a C library and accept that may mean you're stopping them from doing their jobs properly. 2) .sql scrap is the binary installation file? the .sql is the CREATE FUNCTION statement mostly. --- Grace comments : we already grant create privileges on the saslib schema. So it means they can this scrap by themselves ??? No, you have to be a superuser to install C functions, because they have *total* access to all of PostgreSQL and can bypass all security and authentication completely. -- Craig Ringer
Re: [GENERAL] How to create c language in postgresql database. Thanks.
On Tue, Jun 12, 2012 at 11:47 AM, John R Pierce pie...@hogranch.com wrote: On 06/12/12 11:25 AM, leaf_yxj wrote: Thanks. You guys are right. I check the database. The C programm is there. - but why our application team keep ask me to give them the superuser privileges to create the C function. Should they use the superuser to create the C function. if yes , why they need it? yes, only a sql superuser can define a C function, as these have total access to crashing postgres's innards. Not just the innards, but the file system (could be used to overwrite data files), arbitrary system commands, etc.. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Create view is not accepting the parameter in postgres functions
Hi, Is the following postgres function correct? CREATE OR REPLACE FUNCTION MyFun(IdArgs INTEGER) RETURNS SETOF B AS $BODY$ CREATE VIEW A AS SELECT * FROM B WHERE Id = $1; SELECT * FROM B; $BODY$ LANGUAGE 'sql' VOLATILE COST 100; ALTER FUNCTION MyFun(INTEGER) OWNER TO postgres; Where B is a table in the DB schema. Executing select * from MyFun(1) throws the following error: ERROR: there is no parameter $1 LINE 2: ...W A AS SELECT * FROM B WHERE Id = $1; Where as the following function works fine: CREATE OR REPLACE FUNCTION MyFun(IdArgs INTEGER) RETURNS SETOF B AS $BODY$ CREATE VIEW A AS SELECT * FROM B WHERE Id = 1; SELECT * FROM B; $BODY$ LANGUAGE 'sql' VOLATILE COST 100; ALTER FUNCTION MyFun(INTEGER) OWNER TO postgres; Where Id is hardcoded within the function. Is this expected? Please reply. Regards, DP __ DISCLAIMER: This electronic message and any attachments to this electronic message is intended for the exclusive use of the addressee(s) named herein and may contain legally privileged and confidential information. It is the property of Celstream Technologies Pvt Limited. If you are not the intended recipient, you are hereby strictly notified not to copy, forward, distribute or use this message or any attachments thereto. If you have received this message in error, please delete it and all copies thereof, from your system and notify the sender at Celstream Technologies or administra...@celstream.com immediately. __
Re: [GENERAL] Problem installing extensions on Lion
I upgraded my machine from Snow Leopard to Lion, and ran, as usual, into some Postgres update problems. I used Kyngchaos libraries for all frameworks and Postgres. Now, I'd like to install the tablefunc/crosstab function. But the make process gives me this: Looks like your dev env is broken or incomplete. Thanks for that. But what does that mean more exactly? What can I do about it? Thanks for any hints! -- 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] Question about load balance
On 2012-06-12 10:48, John R Pierce wrote: On 06/11/12 11:29 PM, Condor wrote: I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. you handle large databases with a lot of fast disk, and memory, this gets you into the terabytes. clustering/load balancing would not do for this, other than needing MORE fast disk (N replicas require N times the disk system of one database). clustering can provide active/slave failover for high availability, or it can provide replicas for balancing read queries. updates have to be made to all the replicas, so they wont be any faster than a single server (in fact, will be slower due to the overhead of replication -- john r pierceN 37, W 122 santa cruz ca mid-left coast Thanks, I thought so, but I was not sure whether this is a better option. H. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
