Re: [GENERAL] signal 4 recieved when index creation
On my system signal 4 is SIGILL (Illegal instruction), maybe you're triggering a compiler error. Can you run the backend under gdb or get a core file to see where it's dying. Trying it compiled with gcc would be a good test too... On Thu, Dec 09, 2004 at 04:27:18PM +0100, [EMAIL PROTECTED] wrote: hi! I am testing postgreSQL 8.0 rc1 compiled with xlc on AIX5.2. I'm trying to create an index on a 4 500 000 raws table and postmaster recieves signal 4. what can I do to resolve it? -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp65ABbd4TRT.pgp Description: PGP signature
Re: [GENERAL] Shared disk
Nageshwar Rao [EMAIL PROTECTED] writes: Hi, I would like to use shared disk for two Postgresql database. I mean that two Postgresql Database point to same $PGDATA directory. Is this possible in Postgresql Clarification is appreciated. No, this is not possible. You may want to look at replication solutions such as Slony-I. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgres not using shared memory
On Wed, Dec 08, 2004 at 05:53:51PM -0600, Karl O. Pinc wrote: Hi, I can't seem to get postgresql to use shared memory and performance is terrrible. The shared column in free doesn't represent the amount of SysV shared memory used. If the system can't allocate the amount you ask it'll tell you. Running ipcs as root will show you the segment. Perhaps you should tell us what the actual problem is... -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpmzGCrM3OLo.pgp Description: PGP signature
Re: [GENERAL] postgresql and javascript
Hi Robert, This might help you get started: http://developer.apple.com/internet/webcontent/xmlhttpreq.html Keep us posted! Beau ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Reusable database design
Yes actualy.. This is also going to be one of the items on our future FAQ database. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joachim Zobel Sent: Tuesday, December 07, 2004 11:34 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Reusable database design Hi. I can't be the first to think about this. There is a million online shops out there, which all more or less have the same database design. Has anybody thought about creating generic reusable/customizable designs for such cases? Thx, Joachim -- ... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden koennen.- Bertolt Brecht - Leben des Galilei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Significado logo PostgreSQL
el elefante nunca olvide :) the elephant never forgets On Fri, Dec 10, 2004 at 04:02:42PM -0500, Mirko Coz wrote: Amigos: ¿Qué significado tiene el logo de PostgreSQL? Saludos, -- Mirko Coz Berrospi ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] I need your advice
Good day to all ... I hope any one can give me an advice ... to optimize my database... im having a problem when i'm backing up my DB and also using vacuum for my DB... my CPU load goes up and no one can use my DB... most of the transaction comes from web server.. I just need ur good advice ... thank you please check my postgresql.conf -- # # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 300 superuser_reserved_connections = 100 port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # shared_buffers = 600 # min max_connections*2 or 16, 8KB each #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8 # min 4, typically 8KB each # # Non-shared Memory Sizes # #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # # Write-ahead log (WAL) # #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5 # range 1-1000 # #fsync = true #wal_sync_method = fsync # the default varies across platforms: ## fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 # # Optimizer Parameters # enable_seqscan = true enable_indexscan = true enable_tidscan = true enable_sort = true enable_nestloop = true enable_mergejoin = true enable_hashjoin = true effective_cache_size = 1000 # typically 8KB each random_page_cost = 4 # units are one sequential page fetch cost cpu_tuple_cost = 0.01 # (same) cpu_index_tuple_cost = 0.001 # (same) cpu_operator_cost = 0.0025 # (same) default_statistics_target = 10 # range 1-1000 # # GEQO Optimizer Parameters # geqo = true geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_threshold = 11 geqo_pool_size = 1024 # default based on tables in statement, # range 128-1024 geqo_effort = 1 geqo_generations = 0 geqo_random_seed = -1 # auto-compute seed # # Message display # #server_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #silent_mode = false log_connections = true #log_pid = false log_statement = true log_duration = true log_timestamp = true #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #explain_pretty_print = true # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' # # Statistics # show_parser_stats = false show_planner_stats = false show_executor_stats = false show_statement_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # stats_start_collector = false stats_reset_on_server_start = false stats_command_string = false stats_row_level = false stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Misc # autocommit = true #dynamic_library_path = '$libdir' #search_path = '$user,public' #datestyle = 'iso, us' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #client_encoding = sql_ascii # actually, defaults to database encoding #authentication_timeout = 60 # 1-600, in seconds #deadlock_timeout = 1000 # in milliseconds #default_transaction_isolation = 'read committed' #max_expr_depth = 1 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = true #sql_inheritance = true #transform_null_equals = false #statement_timeout = 0 # 0 is disabled, in milliseconds #db_user_namespace = false # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8' - here is my diskspace.. /dev/sdb1 3526172 1132784 2214268 34% / /dev/sda1 248895 8796227249 4% /boot none 2005700 0 2005700 0% /dev/shm /dev/md0 65757260 50992580 11424376 82% /var /dev/sdc1 17409840 13521548 3003916 82% /backup ---(end of
[GENERAL] I need your advice thank you
Good day to all ... I hope any one can give me an advice ... to optimize my database... im having a problem when i'm backing up my DB and also using vacuum for my DB... my CPU load goes up and no one can use my DB... most of the transaction comes from web server.. I just need ur good advice ... thank you please check my postgresql.conf -- # # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 300 superuser_reserved_connections = 100 port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # shared_buffers = 600 # min max_connections*2 or 16, 8KB each #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8 # min 4, typically 8KB each # # Non-shared Memory Sizes # #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # # Write-ahead log (WAL) # #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5 # range 1-1000 # #fsync = true #wal_sync_method = fsync # the default varies across platforms: ## fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 # # Optimizer Parameters # enable_seqscan = true enable_indexscan = true enable_tidscan = true enable_sort = true enable_nestloop = true enable_mergejoin = true enable_hashjoin = true effective_cache_size = 1000 # typically 8KB each random_page_cost = 4 # units are one sequential page fetch cost cpu_tuple_cost = 0.01 # (same) cpu_index_tuple_cost = 0.001 # (same) cpu_operator_cost = 0.0025 # (same) default_statistics_target = 10 # range 1-1000 # # GEQO Optimizer Parameters # geqo = true geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_threshold = 11 geqo_pool_size = 1024 # default based on tables in statement, # range 128-1024 geqo_effort = 1 geqo_generations = 0 geqo_random_seed = -1 # auto-compute seed # # Message display # #server_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #silent_mode = false log_connections = true #log_pid = false log_statement = true log_duration = true log_timestamp = true #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #explain_pretty_print = true # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' # # Statistics # show_parser_stats = false show_planner_stats = false show_executor_stats = false show_statement_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # stats_start_collector = false stats_reset_on_server_start = false stats_command_string = false stats_row_level = false stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Misc # autocommit = true #dynamic_library_path = '$libdir' #search_path = '$user,public' #datestyle = 'iso, us' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #client_encoding = sql_ascii # actually, defaults to database encoding #authentication_timeout = 60 # 1-600, in seconds #deadlock_timeout = 1000 # in milliseconds #default_transaction_isolation = 'read committed' #max_expr_depth = 1 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = true #sql_inheritance = true #transform_null_equals = false #statement_timeout = 0 # 0 is disabled, in milliseconds #db_user_namespace = false # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8' - here is my diskspace.. /dev/sdb1 3526172 1132784 2214268 34% / /dev/sda1 248895 8796227249 4% /boot none 2005700 0 2005700 0% /dev/shm /dev/md0 65757260 50992580 11424376 82% /var /dev/sdc1 17409840 13521548 3003916 82% /backup ---(end of
[GENERAL] PREPARED STATEMENT
Hi there, I want to know that is a posibillity to test if a statement is prepared in PL/PgSQL. I have create a function: . PREPARE PSTAT_SAVE_record(INTEGER, INTEGER, DATE, VARCHAR) AS INSERT INTO table VALUES($1, $2, $3, $4); . When I try to execute it second time I got an error: prepared statement 'PSTAT_SAVE_record' already exists. How can I avoid this error? is there a posibillity to test if a statement was prepared before? Thanks, Nosy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ERROR: relation table does not exist - HELP
That happens when you type: \d From the PSQL sql prompt, like this: connxetl=# \d List of relations Name | Type | Owner ---+--+-- ActiveRange | table | datasync ActiveRangeSequence | sequence | datasync AndScheduleStoplight | table | datasync AndTaskStoplight | table | datasync AssemblyDomain | table | datasync AssemblyGroup | table | datasync AssemblyParentTableGroups | table | datasync AssemblyParentTasks | table | datasync AssemblyProgress | table | datasync AssemblyTasks | table | datasync EmailDomain | table | datasync EmailDomainSequence | sequence | datasync ErrorStatusDictionary | table | datasync Frequency | table | datasync GlobalUniqueSequence | sequence | datasync GroupProgress | table | datasync Groups | table | datasync GroupsSequence | sequence | datasync GuiObjectInfo | table | datasync IntervalFilter | table | datasync IntervalFilterSequence | sequence | datasync IntervalType | table | datasync IntervalTypeSequence | sequence | datasync OfThe | table | datasync OfTheSequenceMUID | sequence | datasync OfTheSequenceSUID | sequence | datasync OrScheduleStoplight | table | datasync OrTaskStoplight | table | datasync ParentAssemblies | table | datasync ParentTableGroups | table | datasync ParentTasks | table | datasync Periods | table | datasync PeriodsSequence | sequence | datasync PriorityDomain | table | datasync PriorityDomainSequence | sequence | datasync Schedule | table | datasync ScheduleSequence | sequence | datasync ScheduleTrigger | table | datasync SourceAssemblyTables | table | datasync SourceTaskTables | table | datasync StartTime | table | datasync StartTimeSequence | sequence | datasync StatusDomain | table | datasync TableCollection | table | datasync TableGroup | table | datasync TableGroupParentAssemblies | table | datasync TableGroupParentTasks | table | datasync TableList | table | datasync TableListSequence | sequence | datasync TableParentAssemblies | table | datasync TableParentTasks | table | datasync TableProgress | table | datasync TargetAssemblyTables | table | datasync TargetTaskTables | table | datasync TaskGroup | table | datasync TaskParentAssemblies | table | datasync TaskParentTables | table | datasync TaskProgress | table | datasync TaskTypeDomain | table | datasync Tasks | table | datasync TimeoutUnits | table | datasync TimeoutUnitsSequence | sequence | datasync WorkflowSettings | table | datasync WorkflowSettingsSequence | sequence | datasync XorScheduleStoplight | table | datasync XorTaskStoplight | table | datasync From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of itamar Sent: Friday, December 10, 2004 7:54 PM To: [EMAIL PROTECTED] Subject: [GENERAL] ERROR: relation table does not exist - HELP when I run select * from table I get this error. ERROR: relation table does not exist Itamar Reis Peixoto +55 34 3238 3845 +55 38 9107 1250 http://www.ispbrasil.com.br
Re: [GENERAL] Install pgsql directory
On Wednesday 08 Dec 2004 2:42 am, Jimmie H. Apsey wrote: I got a server from DELL which contained a Red Hat Linux AS 3 in the box. It contained no postgresql server which I got from an earlier link, http://archives.postgresql.org/pgsql-admin/2003-11/msg00368.php When I did rpm of everything I end up with a non-working postgresql. This may be because there is no /usr/local/pgsql directory. How/what do I need to do to get the directory structure wherein resides the database data. This might help you.. http://wiki.ael.be/index.php/PostgresQL101 Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Checking inequality
I have the following code in one of my trigger functions --- IF old.series_idnew.series_id THEN ... ... END IF; --- The problem is that series_id can change to be NULL in which case I have problems as NULL 7 doesn't return true. What can I do to check inequality even in the case that old or new series_id is NULL? I'd prefer not to do zillion silly comparisons (like IS NULL and IS NOT NULL). Is there an easy way? -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Query is not using index when it should
Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 10 Dec 2004, Tomas [iso-8859-1] Skäre wrote: I have a table that looks like this: Table public.cjm_object Column | Type| Modifiers ---+---+--- timestamp | bigint| not null jobid | bigint| not null objectid | bigint| not null class | integer | not null field | character varying | not null In 7.4.x and earlier, you need to cast the value you're comparing to into a bigint in order to make sure the indexes are used (in your timestamp case it appears to work because the value doesn't fit in a plain integer). 8.0 should handle this better. Thanks, casting worked well for that query. Now, could someone please help me to get this query faster? With the 283465 rows, it takes far too long time, I think. This is on a 2GHz Celeron running Linux 2.6. shared_buffers=1000, sort_mem=1024. select c.* from cjm_object c inner join (select max(timestamp) as timestamp,objectid,field from cjm_object group by objectid,field) t using(timestamp,objectid,field) where 1=1 and data is not null order by objectid,field; QUERY PLAN --- Merge Join (cost=145511.85..150759.75 rows=1 width=54) (actual time=17036.147..20968.811 rows=208246 loops=1) Merge Cond: ((outer.objectid = inner.objectid) AND (outer.?column7? = inner.?column4?) AND (outer.timestamp = inner.timestamp)) - Sort (cost=47007.75..47611.06 rows=241324 width=54) (actual time=5113.099..5586.094 rows=236710 loops=1) Sort Key: c.objectid, (c.field)::text, c.timestamp - Seq Scan on cjm_object c (cost=0.00..5862.65 rows=241324 width=54) (actual time=0.129..1788.125 rows=236710 loops=1) Filter: (data IS NOT NULL) - Sort (cost=98504.09..99212.75 rows=283465 width=48) (actual time=11922.081..12427.683 rows=255001 loops=1) Sort Key: t.objectid, (t.field)::text, t.timestamp - Subquery Scan t (cost=45534.39..51912.35 rows=283465 width=48) (actual time=5484.943..9289.061 rows=255001 loops=1) - GroupAggregate (cost=45534.39..49077.70 rows=283465 width=25) (actual time=5484.925..8178.531 rows=255001 loops=1) - Sort (cost=45534.39..46243.05 rows=283465 width=25) (actual time=5484.285..6324.067 rows=283465 loops=1) Sort Key: objectid, field - Seq Scan on cjm_object (cost=0.00..5862.65 rows=283465 width=25) (actual time=0.124..852.749 rows=283465 loops=1) Total runtime: 21161.144 ms Quick explanation of the query: Each row in the table is a field, which is part of an object. Ex: timestamp objectid field data 11 nametest 11 typesomething 12 nametest2 12 typewhatever Timestamp is when the entry was inserted in the databas. When updating a single field for an object, a new line with the new value is added, data set to NULL if the field is deleted. So the above content could now be: timestamp objectid field data 11 nametest 11 typesomething 12 nametest2 12 typewhatever 21 namenewname 21 typeNULL Now, the query picks out the highest timestamp for each (objectid,field) and then selects all columns for each match, filtering out NULL data and ordering per objectid. Is there any way to make this query faster? I've tried rewriting it, putting the subquery as EXISTS condition, but it doesn't make it faster. I've tried to create different indices, but they don't seem to be used in this query. Greetings, Tomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Shared disk
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Nageshwar Rao) wrote: I would like to use shared disk for two Postgresql database. I mean that two Postgresql Database point to same $PGDATA directory. Is this possible in Postgresql Clarification is appreciated.:p No, you can't do this, at least not if you expect the two database processes to run at the same time. There are High Availability systems that work in somewhat this fashion; both servers access the same disk. But with a very precise set of semantics: - The database filesystem is only mounted on one system at a time - The database process only runs on one system at a time - If the HA system detects that one server has gone down, it kicks it to keep it down, and starts up services on the other one. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/postgresql.html You don't *run* programs on Ultrix. - Mark Moraes Right, you chase them. - Rayan Zachariassen ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Clustering in the presence of hierarchies (fwd)
As far as i can see my mail was not sent to list. I forward it: -- Forwarded message -- Date: Fri, 10 Dec 2004 05:15:42 +0200 (EET) From: Ioannis Theoharis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Clustering in the presence of hierarchies I'd like to ask you about the clustering strategy that postgres implements. Exactly: I have created a hierachy of tables using 'inherits' relationship. I have populated tables with a huge amount of data and then I cluster each table according to an attribute that exists on Root table (hence, this attribute is inherited by all tables). In general, clustering for each of those table means to reorder on disc tuples, in order to be sequential stored. The question is: Does postgres uses the knowledge of the hierarchy structure to reorder tuples of each table to be stored almost after its direct paent-table tuples? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Checking inequality
On Sat, Dec 11, 2004 at 02:42:21PM +0200, Vitaly Belman wrote: IF old.series_idnew.series_id THEN ... The problem is that series_id can change to be NULL in which case I have problems as NULL 7 doesn't return true. What can I do to check inequality even in the case that old or new series_id is NULL? I'd prefer not to do zillion silly comparisons (like IS NULL and IS NOT NULL). Is there an easy way? You could COALESCE the fields to a value that would normally be invalid: IF COALESCE(old.series_id, -1) COALESCE(new.series_id, -1) THEN This assumes that you'd want two NULLs to compare as equal. You could also use CREATE OPERATOR to create an operator that behaves as you'd like. This has been discussed recently; see the list archives. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Checking inequality
COALESCE is good enough for me. Thanks. On Sat, 11 Dec 2004 09:33:32 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Dec 11, 2004 at 02:42:21PM +0200, Vitaly Belman wrote: IF old.series_idnew.series_id THEN ... The problem is that series_id can change to be NULL in which case I have problems as NULL 7 doesn't return true. What can I do to check inequality even in the case that old or new series_id is NULL? I'd prefer not to do zillion silly comparisons (like IS NULL and IS NOT NULL). Is there an easy way? You could COALESCE the fields to a value that would normally be invalid: IF COALESCE(old.series_id, -1) COALESCE(new.series_id, -1) THEN This assumes that you'd want two NULLs to compare as equal. You could also use CREATE OPERATOR to create an operator that behaves as you'd like. This has been discussed recently; see the list archives. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Ridiculous load
On 12/9/04 9:23 AM, Peter Haworth [EMAIL PROTECTED] wrote: It runs RHEL ES v3, kernel 2.4.21-20.ELsmp It's generally a very stable box which runs a number of postgresql instances. But last night we hit very high low averages - 10+, vs the normal 0-2. The culprit appeared to be kswapd, which was using huge amounts of cpu. I'd like to know why! There were some major changes in swap management somewhere in the linux 2.4 kernel (also RH 3.0 ES). I don't off hand remember exactly which level. Under a heavy I/O load, we also saw kswapd going nuts with the 2.4.21 kernel, destroying system performance. The only solution we found was to upgrade to a 2.6 kernel. The problem has not reoccurred since then. Our database is currently about 67 gigabytes and is growing at about 1 million records per day. We are using Postgresql 7.4.x. Wes ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checking inequality
On Sat, 11 Dec 2004, Vitaly Belman wrote: I have the following code in one of my trigger functions --- IF old.series_idnew.series_id THEN ... ... END IF; --- The problem is that series_id can change to be NULL in which case I have problems as NULL 7 doesn't return true. old.series_id IS DISTINCT FROM new.series_id may do what you want depending on how you want NULLs to compare (IS DISTINCT FROM would be false). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Checking inequality
Vitaly Belman [EMAIL PROTECTED] writes: What can I do to check inequality even in the case that old or new series_id is NULL? Try IS DISTINCT FROM. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Google not carrying the pgsql.* hierarchy.
Hi. It seems that when google did its beta update, the pgsql.* hierarchy went missing. Is there anyway to restore it? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] relation sql_features does not exist
Hi, I want to create database in postgreSQL but I get the following error: relation "sql_features" does not exist. As I look for solving this problem I find out that I should add information_schema to the shema search path. but I don't kow how! Elnaz
[GENERAL] vacuumdb
Hi, What are recommendations about running vacuumdb? How frequently it need be executed and how will I know I have to run it. Can I run vaccumdb on production system or I need to do it on DB with no users connected? Thanks, Mark. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Index scan vs. Seq scan on timestamps
Den Tue, Dec 07, 2004 at 03:13:04AM - eller der omkring skrev Andrew - Supernews: On 2004-12-07, Stephen Frost [EMAIL PROTECTED] wrote: Is there a way to say just take the value of this function at the start of the transaction and then have it be constant in a query? Why not use CURRENT_TIMESTAMP, etc., which do exactly that? Because when using transactions, CURRENT_TIMESTAMP does not advance, but is fixed to time of session start /Per ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] question: how to preload data and excute table creation scripts
Hi, I guess is simple, but cannot find out how to run scripts in psql( Linux) What I would like to do is following: 1. Create a table structure from scripts ? 2. Preload data to remote Linux box (IP added to conf file) Thanks, Mark __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] TROUBLE
HOW CAN I RESOLV THIS PROBLEM? (IF I HAVE POSTGRES ON WINDOWS XP) psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? THANK'S _ Platica con tus amigos en linea con T1msn Messenger http://messenger.t1msn.com.mx/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checking inequality
True. It does seem more proper. Thanks. On Sat, 11 Dec 2004 10:57:25 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Dec 11, 2004 at 08:54:40AM -0800, Stephan Szabo wrote: old.series_id IS DISTINCT FROM new.series_id may do what you want depending on how you want NULLs to compare (IS DISTINCT FROM would be false). I forgot about IS DISTINCT FROM. I like that better than using COALESCE, as I had suggested. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Simple function
On Tue, Dec 07, 2004 at 14:49:14 -0300, MaRCeLO PeReiRA [EMAIL PROTECTED] wrote: Hi guys, I have a table with two integer fields (field1 and field2)... I would like to construct a function that perform an operation with this two fiels (field1 + fields2), and return the string value A if the sum is 20, B if the sum is =20 or =50, and finally return C if the sum is 50. Why not use a CASE statement? SELECT id, CASE WHEN field1 + field2 20 THEN 'A' WHEN field1 + field2 50 THEN 'C' ELSE 'B' END FROM mytable; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Checking inequality
On Sat, Dec 11, 2004 at 08:54:40AM -0800, Stephan Szabo wrote: old.series_id IS DISTINCT FROM new.series_id may do what you want depending on how you want NULLs to compare (IS DISTINCT FROM would be false). I forgot about IS DISTINCT FROM. I like that better than using COALESCE, as I had suggested. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Sql performace - why soo long ?
Lada 'Ray' Lostak [EMAIL PROTECTED] writes: I don't understand why following '3' statements are SOO different. We run latest PgSQL, OpenBSD 3.4, AMD XP 2000+ and plenty of RAM... CPU was fully dedicated to PG. Define latest PgSQL. The query plans look like 7.3 or older results to me. 7.4 should do the IN with a hash join ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] When to encrypt
Christopher Browne wrote: Why do you think that's useful in limiting vulnerability? In order for the system to mount the filesystem, the key has got to be there. It does not have to be locally on the system. If I wanted to secure such a system, I would have a private key for decrypting the files stored in a directory server (LDAP or something) in a user or group record. This would allow only authorized individuals to mount the drive. If it has to be highly available, you could also use a machine key to authenticate and obtain the key, which would then be stored in a temporary file. Granted if someone wanted to, they could impersonate the machine and get the key, but it would be a bit more work. In general encrypted filesystems are better at limiting the ability to mount the drive than they are the ability to secure a highly available system against a determined attacker. Of course, security against the most determined and knowledgable attacker may be a pipe dream anyway This is a big problem: You can't just apply cryptography onto things like you would add peanut butter to a sandwich and expect to actually get security. It is eminently easy for a cryptographic system to only provide the _impression_ of security. When I started writing HERMES, I decided to use database native accounts to enforce permissions. As HERMES is web-based, the authentication must occur in every http request. This means that the login and password must be stored somewhere. I toyed with the idea of encrypting the information but decided not to for exactly the reasons that you mention. Indeed I decided that it was better to have the passwords cached in plain text so that the admin would decide to protect them rather than offer a false sense of security by encrypting with a key which an attacker could steal. In the end, I decided that separation of data was a better strategy toward securing the application than encryption. In this case, the login is stored in a cookie, and the password in a PHP session variable. The idea was that the cookie could be read or the session variable, but putting them together would require reading both. My strategies in web application security can be summed up in the following ideas: 1) Separate sensitive information so that it cannot be easily put together. (this is app level and not usually db level) 2) Push security enforcement back as far as possible. Let the database server maintain its security. If it can be exploted, you are toast anyway. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Regarding Postgres installation and administration on linux suse 9.0
Dear Sir /Madam, Weare working on Linux suse 9.0 and we have installed Postgres 7.3 through yast with the privileges of "root" user. But we are not able to start / configure the postgres database, Kindly help us in configuring and starting the same database and how can we create database and use it. Wewill be very thankful to you prompt help. Thanks RegardsVikas Kumawat
Re: [GENERAL] table with sort_key without gaps
On Thu, Dec 09, 2004 at 18:32:19 +0100, Janning Vygen [EMAIL PROTECTED] wrote: id should be positive id should not have gaps within the same account id should start counting by 1 for each account i cant use sequences because they are producing gaps and doesn't start counting by 1 for each account and i dont want to use postgresql array type for various reasons. for this model to function you need a lot of sophisticated plpgsql function to insert, move or delete entries to keep I doubt you want to use this model if you are going to be deleting records. - did anyone implemented a table like this and wrote some custom functions/triggers for inserting, deleting, moving and so on? If yes it would be nice if he/she is willing to sahre the code with me. If you aren't deleting records and you don't have a lot of concurrent requests, you can lock the table and select the current max id for an account and add 1 to get the next id for for that account. - did anyone implemented a table like this and came to the conclusion that this shouldn't be done for any reasons out of my sight? (i don't bother about updating a primary key) Why are you doing this? Normally uniqness of an ID is good enough. If you don't need to worry about gaps, you could use one sequence for the entire table to generate IDs. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Regarding Postgres installation and administration on linux suse 9.0
On Dec 10, 2004, at 4:26 AM, Vikas Kumawat wrote: x-tad-biggerDear Sir /Madam,/x-tad-bigger x-tad-biggerWe are working on Linux suse 9.0 and we have installed Postgres 7.3 through yast with the privileges of /x-tad-biggerx-tad-biggerroot/x-tad-biggerx-tad-bigger user. But we are not able to start / configure the postgres database, Kindly help us in configuring and starting the same database and how can we create database and use it. We will be very thankful to you prompt help./x-tad-bigger x-tad-biggerThanks Regards/x-tad-bigger x-tad-biggerVikas Kumawat/x-tad-bigger The postgres users should own the data directory, but otherwise should not have administrative privileges. What is the specific error you are getting?
Re: [GENERAL] question: how to preload data and excute table creation scripts
On Wed, Dec 08, 2004 at 08:58:49 -0800, Mark [EMAIL PROTECTED] wrote: Hi, I guess is simple, but cannot find out how to run scripts in psql( Linux) What I would like to do is following: 1. Create a table structure from scripts ? 2. Preload data to remote Linux box (IP added to conf file) You can use psql to do things like this. psql ddl.sql psql copy.sql (Where dd.sql holds the ddl creation commands and copy.sql holds the copy commands to load the data. You can look at pg_dump output for examples.) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] What's faster
On Fri, Dec 10, 2004 at 06:15:50PM -0800, Eric Brown wrote: Option 1: create table a (id serial, hosts text[]); OR Option 2: create table a (id serial); create table hosts (id int references a, host text); Option 2 will save a lot of developer query time, as it's much more standard. If you need a VIEW like table a, it's easy to do like this: CREATE VIEW view_a AS SELECT a.id, ARRAY(SELECT host FROM hosts WHERE id = a.id) FROM a; Table 'a' will have about 500,000 records. There will probably be about 20 reads for every write. Each id has approximately 1.1 hosts. If I use the array (option 1), I'll have to loop over the elements of the array to see if I have a match when querying a given id. This isn't hard, but it means that SELECT will always return 1 record when, in option 2, it might return 0 records and only have accessed the indexes. Given the indexes that will be built and disk pages used (cached or otherwise), which mechanism would be faster for searching. It's a lot easier to search under option 2, and besides, speed isn't everything ;) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] vacuumdb
On Wed, Dec 08, 2004 at 09:45:53 -0800, Mark [EMAIL PROTECTED] wrote: Hi, What are recommendations about running vacuumdb? You need to VACUUM tables to reclaim space created by DELETE and UPDATE commands. You need to run ANALYZE tables when their distribution of data changes. If you are doing a VACUUM, you usually want to ANALYZE at the same time, but you may want to do separate ANALYZEs if you do lots of INSERTs compared to DELETEs and UPDATEs. How frequently it need be executed and how will I know I have to run it. That depends on your application. Can I run vaccumdb on production system or I need to do it on DB with no users connected? You can run on production, but this can be a problem if your disk bandwidth is already saturated when you run the vacuum. You can also look at the pg_autovacuum contrib package that is included with recent versions of postgres. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Sql performace - why soo long ?
Define latest PgSQL. The query plans look like 7.3 or older results to me. 7.4 should do the IN with a hash join ... latest is the last minor release, which I know worked well for us :) So, I have latest from 7.3.x... I will try to upgrade to latest 7.4.x and try all SQL's again. Thank you :) Please, do you know if exists some 'up-to-date' doc about curret state of planner ? I didn't found something like that. Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql and javascript
Javascript is not only client side. IIS has two script languages installed as default: VBScript and JScript(MS version of Javascript). Indeed at my work I have already written hundreds of javascript server pages. We use MS SQL Server. I understand Robert Stewart's question, and the only advice I have is look at the ADO reference site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscmicrosoftadoprogrammersreference.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefodbcprovspec.asp If Postgresql can expose an ODBC interface then it will work. Regards, Clodoaldo Pinto --- Najib Abi Fadel [EMAIL PROTECTED] escreveu: Hi i don't see any practical use of javascript with Postgresql, since a java script is on the client side. Unless you want each client machine to have its own database ! Najib. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 5:27 PM Subject: [GENERAL] postgresql and javascript Does anyone know how to connect javascript to a postgresql database Please send example if anyone has done it thanks Robert Stewart Network Eng Commonwealth Office of Technology Finance and Administration Cabinet 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED] ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index scan vs. Seq scan on timestamps
Den Tue, Dec 07, 2004 at 03:13:04AM - eller der omkring skrev Andrew - Supernews: On 2004-12-07, Stephen Frost [EMAIL PROTECTED] wrote: Is there a way to say just take the value of this function at the start of the transaction and then have it be constant in a query? Why not use CURRENT_TIMESTAMP, etc., which do exactly that? Oops, CURRENT_TIMESTAMP *does* advance from transaction to transaction. As you can see from my previous mail, I thought it was fixed to the time of session start. CURRENT_TIMESTAMP is fine for me then and I will not use timeofday()::timestamptz /Per ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] relation sql_features does not exist
On Tue, Dec 07, 2004 at 10:42:29AM +0330, Elnaz Shafipour wrote: I want to create database in postgreSQL but I get the following error: relation sql_features does not exist. How are you creating the database, and is that the operation that fails or do you get the error when doing something after you've created the database? Please copy paste the exact commands you're running and the output. What version of the client are you running? The startup banner should say, or you can run psql --version (or createdb --version if that's what you're running). What version of the server are you running? You can find out by executing the query SELECT version();. As I look for solving this problem I find out that I should add information_schema to the shema search path. but I don't kow how! Let's find out what the problem is before attempting a solution that may or may not be appropriate or necessary. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Shared disk
No, you can't do this, at least not if you expect the two database processes to run at the same time. There are High Availability systems that work in somewhat this fashion; both servers access the same disk. But with a very precise set of semantics: - The database filesystem is only mounted on one system at a time - The database process only runs on one system at a time - If the HA system detects that one server has gone down, it kicks it to keep it down, and starts up services on the other one. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/postgresql.html You don't *run* programs on Ultrix. - Mark Moraes Right, you chase them. - Rayan Zachariassen ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] trouble with on insert rule via libpg-perl
On Fri, Dec 10, 2004 at 11:26:09PM -0500, Ron Peterson wrote: I have a simple table, a view, and an on insert rule. These work fine, ordinarily. But when I attempt to to insert a value into thesis_ps_v via libpq_sql, nothing happens. Fixed. I didn't set the schema search path properly in my perl code. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] ERROR: relation table does not exist - HELP
On Sat, 2004-12-11 at 01:54 -0200, itamar wrote: when I run select * from table I get this error. ERROR: relation table does not exist The table name is is folded to lowercase, unless it is in quotes. so if the name of the table is TABLE, you need select * from TABLE Is this your problem ? gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to get rid of notices for create table?
--- Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Dec 10, 2004 at 06:31:15PM -0300, Pablo Santiago Blum de Aguiar wrote: I'm getting boring notices when creating tables: [snip] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index pk_cliente for table cliente NOTICE: CREATE TABLE / UNIQUE will create implicit index uk_email for table cliente CREATE TABLE Got a few questions: 1 - What those 2 notice messages mean? They're informational messages. PostgreSQL is telling you that it's creating indexes that you didn't explicitly request. Ok. I read the CREATE INDEX manual section and I could create an index but then I get an error message telling that relation pk_cliente already exists when creating the table. How do I explicitly request PostgreSQL to create those index for the table? Thanks for your help. = -- .''`. Pablo Aguiar pabloaguiar at yahoo.com : :' : Proud Debian GNU/Linux Admin and User `. `'` GNU/Linux User #346447 - PC #238975 `- Debian, when you have better things to do than fix a system. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How to get rid of notices for create table?
On Sat, Dec 11, 2004 at 02:20:06PM -0800, Pablo Santiago Blum de Aguiar wrote: Ok. I read the CREATE INDEX manual section and I could create an index but then I get an error message telling that relation pk_cliente already exists when creating the table. How do I explicitly request PostgreSQL to create those index for the table? You could omit the PRIMARY KEY and UNIQUE constraints in the table definition and create UNIQUE indexes after creating the table. But what problem are you trying to solve? Your original message asks how to get rid of the NOTICE messages for the implicitly-created indexes; you can do this by setting configuration variables that tell PostgreSQL what level of messages you want to see. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] postgresql and javascript
Chris Travers wrote: Geoffrey wrote: Chris Smith wrote: [EMAIL PROTECTED] wrote: Does anyone know how to connect javascript to a postgresql database You can't connect javascript to any sort of database. Actually you can, with server side javascript, although I don't know if it supports postgresql. It does support odbc. Javascript is simply designed for lightweight scripting against a pre-existing object set provided by the host program. I wouldn't recommend it, but we did have a full database implementation strictly using server side javascript. It was a time reporting system and it worked okay. The server side stuff seemed to be quite a load on the server though. We are using to connect to an existing Oracle database. Nothing else, html and server side javascript. -- Until later, Geoffrey ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql and javascript
Clodoaldo Pinto wrote: Javascript is not only client side. IIS has two script languages installed as default: VBScript and JScript(MS version of Javascript). Only does you good if you're running on a IIS platform. The server side Javascript engine worked on the Netscape web server product, which was then moved to Iplanet, Sun product I think. -- Until later, Geoffrey ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] using postgresql functions from php
On Tue, Dec 07, 2004 at 02:35:58PM -0500, [EMAIL PROTECTED] wrote:
I have created a function in postgresql
See comments below.
CREATE OR REPLACE FUNCTION public.insert_vpn_masteraccount(varchar, varchar,
varchar, varchar, varchar, varchar, varchar, varchar, varchar)
RETURNS varchar AS
'
DECLARE
You have an empty DECLARE section -- you can omit it if you don't
plan to use it.
BEGIN
insert into masteraccount(fname,midint,lname,username,atype)
values($1,$2,$3,$4,$5);
insert into passwd(u_id,currentpwd) values((select max(u_id) from
masteraccount where username=$4),$6);
What's the purpose of the select max(u_id) subquery? If
masteraccount.u_id is a SERIAL column then you could do this
instead:
INSERT ... VALUES (currval(''masteraccount_u_id_seq''), $6);
Note the two single quotes, which are necessary if the function
body is defined in quotes. PostgreSQL 8.0 will offer an alternate
way to quote strings so you won't have to do this.
insert into ipinfo(u_id,ipaddress,atype) values((select max(u_id) from
masteraccount where username=$4),$7,$5);
insert into userinfo(u_id,agency,user_email) values((select max(u_id) from
masteraccount where username=$4),$8,$9);
The same comment about using currval() applies to these inserts.
return masteraccount.u_id where masteraccount.username=$4;
Beware: the above line depends on the add_missing_from configuration
variable being turned on. It would be safer to add the appropriate
FROM clause, but you could probably replace the entire statement
with:
return currval(''masteraccount_u_id_seq'');
END;
'
LANGUAGE 'plpgsql' VOLATILE;
I can insert data using this function with a sql statement by typing
select
insert_vpn_masteraccount('tom','d','johnson','tomd.johnson','V','1234','DHCP
','AGR','[EMAIL PROTECTED]'
this works fine
but when I try to do it from a php web page using
$enter = $db_object-query(SELECT
insert_vpn_masteraccount('$fname','$mi','$lname','$acc_type','$passwd','$ip'
,'$agency','$contact'));
This does not work
Define does not work -- what happens? One problem I see is that
the call in your PHP code passes only 8 arguments, whereas the
function expects 9. The SQL statement that works passes 9 arguments.
I'll caution you about something else: SQL injection. Have the
variables $fname, $mi, etc., been properly escaped to prevent
malicious code from being injected into your SELECT statement?
Malicious intent aside, a name like O'Reilly could cause problems
if the variables aren't escaped. If your database interface supports
placeholders then I'd suggest using them (unless they're known to
be broken).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Index scan vs. Seq scan on timestamps
On Tue, Dec 07, 2004 at 09:25:20AM +0100, Per Jensen wrote: Den Tue, Dec 07, 2004 at 03:13:04AM - eller der omkring skrev Andrew - Supernews: On 2004-12-07, Stephen Frost [EMAIL PROTECTED] wrote: Is there a way to say just take the value of this function at the start of the transaction and then have it be constant in a query? Why not use CURRENT_TIMESTAMP, etc., which do exactly that? Because when using transactions, CURRENT_TIMESTAMP does not advance, but is fixed to time of session start CURRENT_TIMESTAMP is fixed to the time of transaction start, not session start; this is documented and observable behavior. Can you demonstrate otherwise? If so, on what version of PostgreSQL? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Problems with information_schema
Marcel Gsteiger [EMAIL PROTECTED] writes: Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet? AFAIK all the information_schema changes since 7.4 are quite intentional. Rather than pointing out that it's changed, you need to show us an example where you think the new behavior is wrong. (And please do so ASAP, because if 8.0 goes final next week, it'll be quite hard to fix later ...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Join on virtual table
Rory Campbell-Lange wrote: Hi. I'd like to return a result set from a plpgsql function constructed out of a 'virtual table' joined to an actual table, and struggling to find a sane approach. I have a table 'recs' with records like this. day | nums --- 2 | 1 5 | 3 2 | 2.5 For a particular month in the year I would like to generate all the days in the month into a virtual table. 'virt' vday --- 1 ... omitted ... 30 I would like a result set something like this: day | nums --- 1 | 0 2 | 3.5 3 | 0 4 | 0 5 | 3 6 | 0 ... etc. You mean like this? create table recs (day int, nums float); insert into recs values(2,1); insert into recs values(5,3); insert into recs values(2,2.5); CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS setof int AS ' BEGIN FOR i IN $1..$2 LOOP RETURN NEXT i; END LOOP; RETURN; END; ' LANGUAGE plpgsql; select f1, sum(coalesce(nums, 0)) from generate_series(1, 6) as t(f1) left join recs on f1 = day group by f1; f1 | sum +- 1 | 0 2 | 3.5 3 | 0 4 | 0 5 | 3 6 | 0 (6 rows) BTW, as of 8.0.0, generate_series() is built in. HTH, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgres 7.4.6 x86_64 RPMS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Fri, 10 Dec 2004, John Allgood wrote: I am looking for Postgres 7.4.6 x86_64 RPMS for Redhat ES 3.0. I Think 7.4.6 will be included in Redhat ES 4.0 but I am not sure when that will be released and we may not upgrade at that time. I have found RPMS for Fedora Core 3 but they want work. I think you could pick up an SRPM and rebuild it on RHES 3.0. Here it is: ftp://ftp.postgresql.org/pub/binary/v7.4.6/srpms/redhat/rhel-3/postgresql-7.4.6-2PGDG.src.rpm rpmbuild --rebuild postgresql-7.4.6-2PGDG.src.rpm Please let us know if you experience any problems while building the RPMs. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBuuwOtl86P3SPfQ4RAhp8AJ9P4NRB1L/rVbkBW3GX80LG8TFLSwCghvUy JrovWjft5rbyd0huSNEK3L8= =6NaB -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] No mailing list posts
On Wed, Dec 08, 2004 at 08:41:19 -0600, Don Isgitt [EMAIL PROTECTED] wrote: I have received no posts from GENERAL since yesterday morning; is the list broken? Thank you. When you suspect a problem with a list it is generally best not to send a message to the list asking if it is down or as a test message. Depending on what is going on these messages may get through to the list and add noise and are pretty much useless for telling whether or not there is a problem for a high volume list. If you want to check whether the problem is with the list or your subscription you should try looking at the archives (no new archives suggests a list problem, up to date archives suggests a subscription or email blocking problem with your address). You can also use the list administration tools to try to confirm your subscription and make sure the list server can get messages to you. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Best practice in postgres
Hi All, I'm new to postgres, so I need your help. We are in the process of migrating from oracle to postgres. DB size is about 400gb. My question is about schemas in oracle and postgres. Does every schema in oracle becomes a separate database in postgres ? OR it is still like oracle, where all schemas are part of big database ? Also, I thought vacuuming will be easier if oracle schema becomes database in postgres. For example in our case each schema is like 80-90 gb, smaller compare to vacuuming on 400gb. Thanks, ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Select after insert to the unique column
Hello,
I have a following table with unique column:
CREATE TABLE UNIQUE_COLUMN_TEST (
TEST_ID INTEGER,
CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID)
)
Then I insert few records into the table, and then I try to insert
duplicate record. There is correct error occured:
org.postgresql.util.PSQLException:
ERROR: duplicate key violates unique constraint test_id_uq
Then I want to process command
select count(*) from UNIQUE_COLUMN_TEST
that I want to know how many records was already inserted before id
faied.
But when I try to process that SELECT COUNT(*), there is error
occured again:
org.postgresql.util.PSQLException:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
How can I solve this?
Thank you in advance,
with best regards,
Julian Legeny
All this work is processed within 1 transaction and here is the
code:
// insert value
m_transaction.begin();
try
{
Connection connection = null;
try
{
// try to insert 5 correct records
for (iCounter = 1; iCounter 6; iCounter++)
{
insertStatement = m_connection.prepareStatement(
insert into UNIQUE_COLUMN_TEST (TEST_ID) values (?));
insertStatement.setInt(1, 100 * iCounter);
insertStatement.executeUpdate();
}
// insert duplicite value into unique column
try
{
insertStatement = m_connection.prepareStatement(
insert into UNIQUE_COLUMN_TEST (TEST_ID) values (?));
insertStatement.setInt(1, 100);
insertStatement.executeUpdate();
}
catch (SQLException sqlExc)
{
try
{
// THIS EXCEPTION IS EXPECTED
// now try to find out how many records were
// already inserted befor it failed
selectStatement = m_connection.prepareStatement(
select count(*) from UNIQUE_COLUMN_TEST);
// !!! AT THE FOLLOWING LINE IT FAILED AGAIN !!!
// cause: current transaction is aborted, commands
//ignored until end of transaction block
rsResults = selectStatement.executeQuery();
if (rsResults.next())
{
assertEquals(Incorrect number of selected items,
5, rsResults.getInt(1));
}
}
catch (SQLException sqlExc1)
{
throw new SQLException();
}
finally
{
rsResults.close();
}
}
}
finally
{
DatabaseUtils.closeStatement(insertStatement);
DatabaseUtils.closeStatement(selectStatement);
}
m_transaction.commit();
}
catch (Throwable throwable)
{
m_transaction.rollback();
throw throwable;
}
...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] question: how to preload data and excute table creation scripts
On Wed, Dec 08, 2004 at 08:58:49AM -0800, Mark wrote: I guess is simple, but cannot find out how to run scripts in psql( Linux) Create a file with the SQL statements you'd like to run. There are several ways to get psql to read the file: Redirection: psql foo.sql Command-line option: psql -f foo.sql Include into the current psql session: psql \i foo.sql See the psql documentation for more info. What I would like to do is following: 1. Create a table structure from scripts ? 2. Preload data to remote Linux box (IP added to conf file) See above. For bulk loads, consider using COPY instead of INSERT since COPY is faster. You can find additional advice in the Populating a Database section of the Performance Tips chapter in the PostgreSQL documentation. Consider using transactions in your script -- that way if you make a mistake and psql raises an error, you're not left with the work half done. See also the ON_ERROR_STOP variable in the psql documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Select after insert to the unique column
On Wed, Dec 08, 2004 at 14:50:04 +0100, Julian Legeny [EMAIL PROTECTED] wrote: Hello, Then I want to process command select count(*) from UNIQUE_COLUMN_TEST that I want to know how many records was already inserted before id faied. But when I try to process that SELECT COUNT(*), there is error occured again: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block How can I solve this? Depending on what you really want to do, you could do each insert in its own transaction. If you don't want any of the inserts to succeed if there are problems, then you should do the counting in the application doing the inserts. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Questions on stored-procedure best practices
file-names: I know it doesn't matter, but mostly it seems to make sense to put stuff in .sql files. I've seen some reference to people putting stuff in .sp files. What works best for people in terms of organization? GnuMed uses *.sql and we haven't had anyone wondering about it if that's an indicator of it having immediate appeal. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Best practice in postgres
Nilesh Doshi wrote: My question is about schemas in oracle and postgres. Does every schema in oracle becomes a separate database in postgres ? OR it is still like oracle, where all schemas are part of big database ? You can have multiple schemas in a single PostgreSQL database. Unlike Oracle, in PostgreSQL when you add a user that does not automatically generate a schema - that is a separate operation. See CREATE SCHEMA. Also, I thought vacuuming will be easier if oracle schema becomes database in postgres. For example in our case each schema is like 80-90 gb, smaller compare to vacuuming on 400gb. I'm very new to PostgreSQL myself, so this is definitely not expert advice. But the load imposed by vacuum is directly related to update activity. So if your database is fairly static, you probably won't see much benefit to splitting out schemas into separate DBs. Also, 8.0 has an auto-vacuum daemon that is supposed to lighten the load incurred by vacuum by checking frequently in the background. -- Guy Rouillier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
