Re: [GENERAL] [HACKERS] optimization join on random value

Yahooo ! Many thanks ! soe=# explain EXECUTE test( random()*45000 ); QUERY PLAN - Index Scan using addresses_cust_ix on addresses (cost=0.43..16.48 rows=3 width=84) Inde

Re: [GENERAL] pg_last_xact_replay_timestamp lies

-f54ue6xtkh4nerpvsqn...@mail.gmail.com On 06/15/2015 04:24 AM, Michael Paquier wrote: On Mon, Jun 15, 2015 at 9:04 AM, Anton Bushmelev wrote: Hello, thank t for response, measure in bytes may bemore correct, but to bring it to the customer? :) I think it is easier to say that the standby database lags behind

[GENERAL] how to use array with "holes" ?

Hi. I want to use array for store some values (bytes_in and bytes_out) and use array index as "id". But I got an errors... My example function extract traf_id, bytes_in, bytes_out and try to fill an array, like this CREATE OR REPLACE FUNCTION myf_test() RETURNS void AS $$ DECLARE p_tmp RECORD; p

Re: [GENERAL] how to use array with "holes" ?

> May I ask some more complex? I want to use ONE multidimensial array - > the "id", "bytes_in" and "bytes_out". By another words, I need an > array, each element of which must contain 3 values: ttc_id, bytes_in, > bytes_out. > > I think it can be done like this: It's problem. You have to wait for

Re: [GENERAL] how to use array with "holes" ?

you have to initialise array before using. Like: declare a int[] = '{0,0,0,0,0, .}'; begin a[10] := 11; Ok, I got it, thanks! Now I can work with simle arrays. May I ask some more complex? I want to use ONE multidimensial array - the "id", "bytes_in" and "bytes_out". By anothe

[GENERAL] ERROR: out of memory

Hi. I got an error when I try to VACUUM ANALYZE table. # VACUUM ANALYZE n_traf; ERROR: out of memory DETAIL: Failed on request of size 536870910. In logfile: TopMemoryContext: 33464512 total in 12 blocks; 10560 free (61 chunks); 33453952 used TopTransactionContext: 8192 total in 1 blocks; 7688

[GENERAL] JOIN work somehow strange on simple query

Hi. I have a performance problem with this simple query: SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id) WHERE n_logins.account_id = '1655' ORDER BY collect_time LIMIT 1; Limit (cost=0.00..2026.57 rows=1 width=8) (actual time=5828.681..5

Re: [GENERAL] JOIN work somehow strange on simple query

Hi. I have a performance problem with this simple query: SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id) WHERE n_logins.account_id = '1655' ORDER BY collect_time LIMIT 1; I must add that is occurs when there is no rows in n_traffic for these login_id's. Where there is at least

[GENERAL] Why DISTINCT ... DESC is slow?

Hi. With this table (about 800 000 rows): =# \d n_traffic Table "public.n_traffic" Column|Type | Modifiers --+-+-- login_id | integer | not n

Re: [GENERAL] Why DISTINCT ... DESC is slow?

> Why? 768 rows is about 1000 times smaller than entire n_traffic. And > why Index Scan used without DESC but with DESC is not? For the DESC version to use the index try "login_id DESC collect_time DESC" - so both are reversed. Yes, it helps! But If you want the most recent collect_time for e

Re: [GENERAL] Why DISTINCT ... DESC is slow?

> =# \d n_traffic > Table "public.n_traffic" >Column|Type | Modifiers > --+-+-- > login_id | integer | not null > traftype_id | integer

[GENERAL] Re: [pgsql-ru-general] index на таблицу с ~ 700 000 записей: чем занят постгрес???

Вот утром смотрю - сделался индекс (не знаю ск-ко часов он делался, но тем не менее). То есть ошибок-то нету, в логе ругательств не наблюдается. Но вопрос остаётся. Вопрос решился. Дело было в системных лимитах на разделяемую память и семафоры, а возможно и в размере кэша файловой системы. Опред

[GENERAL] Re: [pgsql-ru-general] index на таблицу с ~ 700 000 записей: чем занят постгрес???

Sorry, wrong list. > Вот утром смотрю - сделался индекс (не знаю ск-ко часов он делался, но > тем не менее). То есть ошибок-то нету, в логе ругательств не > наблюдается. > Но вопрос остаётся. Вопрос решился. Дело было в системных лимитах на разделяемую память и семафоры, а возможно и в размере

[GENERAL] pg_last_xact_replay_timestamp lies

Hello, dear guru =) help me to fix situation when no change made on primary and standby show lag more than 15 minutes: master : postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]+-- pid | 18553 usesysid | 117942 usename | rep

Re: [GENERAL] pg_last_xact_replay_timestamp lies

Hello, thank t for response, measure in bytes may bemore correct, but to bring it to the customer? :) I think it is easier to say that the standby database lags behind master no more than 15 minutes, than the fact that it differs for 1 megabyte. ps: sorry for my English On 06/15/2015 02:57 AM,

Re: [GENERAL] @ operator

Thanks for reply, it is clear now =) On 09/17/2015 08:34 PM, David G. Johnston wrote: On Thursday, September 17, 2015, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Thursday, September 17, 2015, Антон Бушмелев > wrote: Hello, google nothing for @ operator =(

[GENERAL] GIN Indexes: Extensibility

Dear All, Here is what I have: user=# create table FOO (key jsonb); CREATE TABLE user=# insert into FOO(key) values ('[2014]'), ('[2015]'), ('[2016]'), ('[2014, 2]'), ('[2014, 2, 3]'), ('[2014, 3]'), ('[2014,2,4]'), ('[2014, 2,4]'), ('[2014,3,13]'), ('[2014, 2, 15]'); INSERT 0 10 user=# SELECT

Re: [GENERAL] GIN Indexes: Extensibility

Vick, you are right. That’s why I’d wish to add some custom code to MY PostgreSQL instance and set such a sort order, which is optimal for my application. On Jul 27, 2016, at 17:44, Vick Khera wrote: > On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich > wrote: >> In my situation t

Re: [GENERAL] GIN Indexes: Extensibility

approach! Regards, Anthony Ananich http://ananich.pro On Jul 27, 2016, at 18:00, Paul Jungwirth wrote: > On 07/27/2016 07:44 AM, Vick Khera wrote: >> On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich >> wrote: >>> In my situation this order is invalid. Obviously, year 2016 should

Re: [GENERAL] Code of Conduct: Is it time?

em is codifying such rules and that these same people who have the problem will likely argue such rules to the death. Maybe the present discussion is an example. Jeff Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www

[GENERAL] should the postgres user have a password?

l (or psql, or whatever) on the CL. There is no option in the docs... Would it be better to remove the password (if so, any pointers, I couldn't find that either!) and make postgres only able to connect via pipes? Thanks again, Anton ps. I did google but obviously couldn't find the right co

Re: [GENERAL] should the postgres user have a password?

Sorry .pgpass :-( Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?

be pushing the limits, then compiling your own versions is not going to be an issue... Just my 2c Cheers Anton ps. I know, when a new version comes out so often it is soo hard to resist! ---(end of broadcast)--- TIP 2: Don't 'ki

Re: [GENERAL] URGENT: Whole DB down ("no space left on device")

t-get or yum away then... why not just go with what *is* there? Surely it must be being used by more people, if not, why aren't the others in the repos? Random ramblings! Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] OK, when 8.3 is coming?

Hi, Has someone stated when is Postgresql 8.3 coming? Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Please change default characterset for database cluster

Hi, Get a VPS - Virtual Private Server. Mine is 29$ and it is fine for 480MB RAM and enough disk space. I am a full admin on my server, so I install and configure Postgresql without problem. YES! I agree that the default encoding must be UTF-8. I started using Postgresql, cause I had problems w

[GENERAL] Sql problem

Hi, Can you tell me more about Postgresql string functions? I want to replace a column text "1/2/3/8/" to the corresponding values to these values like "A, B, C, D". Cheers, Anton Andreev ---(end of broadcast)--- TIP 4: H

[GENERAL] day of week

Hi, I have records with date column. Is there a way I can get which day of week this date is? Cheers, Anton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining

[GENERAL] safely increase a single column integer value

Hi, What is the best way from concurrency point of view to increase a integer value from a table? Suppose you count every postback from all the users that are currently browsing your web-site. Cheers, Anton ---(end of broadcast)--- TIP 9

[GENERAL] Pg_catalog reference

ion in thi case? Any help appriciated. Thanks. Best regards, Anton Nikiforov ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] How to retrieve values of 'array' field using C library (libpq)

Hi. I use libpq in my program. And I can retrieve int and symbolic fields as simple as int unameFN, moneyFN... char *unamePTR, *moneyPTR... unameFN = PQfnumber(res, "uname"); moneyFN = PQfnumber(res, "money"); unamePTR = PQgetvalue(res, 0, unameFN); moneyPTR = PQgetvalue(res, 0, moneyFN); themo

[GENERAL] Drastic select count performance hit when jsonb GIN indices are present

ndex rather than just a scan. Now as I'm completely new to jsonb and GIN there are probably lots of better ways to do this and configure the server (a VM on my laptop...) but it seems surprising that adding an index should ever drastically reduce select performance! I also tried an index on the event object, with and without jsonb_path_ops mgevents=# create index idx_myevents_event_type_path on myevents using gin ((event -> 'event') jsonb_path_ops); But it just ends up doing a scan anyway, so I'm probably doing something wrong. Any pointers? Thanks! Anton

Re: [GENERAL] Drastic select count performance hit when jsonb GIN indices are present

? Thanks again, Anton On 26 December 2014 at 14:19, Maxim Boguk wrote: >-> Bitmap Heap Scan on myevents (cost=35.80..3615.09 rows=3716 > width=0) (actual time=351.510..77669.907 rows=1417152 loops=1) > >> Recheck Cond: (event @> '{"event": &q

[GENERAL] returning a resultset from a function

Hi, How do I return a result set? Is there a better way in 'plpgsql' than the one described below? I do not want to make a select from a function(which pretty useful in many cases): SELECT * FROM getfoo(); , but I want to just call the function with SELECT getfoo(); --DROP FUNCTION getfoo();

[GENERAL] shared_buffers and shmmax what are the max recommended values?

rk_mem = 262144 max_fsm_pages = 30 max_fsm_relations = 1 Any suggestions most welcome. Cheers Anton -- 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] shared_buffers and shmmax what are the max recommended values?

ally by content writers... and I'm not even certain where it is coming from but that is another story!), and yes checkpoint_segments is at the default... if I increase to 10 or so will that be better? btw, we have a warm standby via wal copies if that makes a difference... Cheers Anton -- Se

Re: [GENERAL] shared_buffers and shmmax what are the max recommended values?

That's crystal. Thanks for your advice! Cheers Anton On 07/03/2008, Greg Smith <[EMAIL PROTECTED]> wrote: > On Fri, 7 Mar 2008, Anton Melser wrote: > > > > There is actually quite a bit of write (at least the dump is increasing > > far more than what is being a

[GENERAL] Question about the cluster command

a specific company ordered by date as fast as possible (to generate charts). Does autovacuum include reclustering? Cheers, Anton Andreev -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3

ise and respect the logic in doing this but not having a fallback (even if it means recompiling from source) is painful! Am I really stuck with pre-8.3? Cheers Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3

On 21/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anton Melser" <[EMAIL PROTECTED]> writes: > > ... But it is COMPLETELY out of the > > > question to redo the db abstraction layer, and without these implicit > > casts that is what will be need

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

I did, of course, not follow the instructions and just blinding applied them all, but from reading them it doesn't look like the issue here. Does this error mean there are too many operators or not enough? Meaning another function + cast would solve it? Or maybe making the function more complex

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

ve me more newbie instructions? :-) I suppose you are talking about anytextcat(anynonarray, text) and textanycat(text, anynonarray) But I can't see anywhere obvious where I can "deactivate" them... I looked for likely suspects in pg_operator, pg_cast... but I'm not really sure wha

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

select 1 || '/'::text ERROR: operator is not unique: integer || text LINE 1: select 1 || '/'::text ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Am I in between a rock and a hard place here? Thanks again, An

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

On 25/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anton Melser" <[EMAIL PROTECTED]> writes: > > >> You'd have to do something like > >> DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; > >> since there is

[GENERAL] How to "use" database?

Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows? I can not use "USE Northwind;" or "\connect Northwind;"? Cheers, Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL] How to "use" database?

create a new database you can not access it by name cause you receive an error "cross database referencing is not allowed". Cheers, Anton Shane Ambler wrote: Anton Andreev wrote: Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows

Re: [GENERAL] database 1.2G, pg_dump 73M?!

I may need to do some > additional things to enable it. This is probably far too late but anyway. You do indeed have to enable autovacuum with 8.2, as it isn't enabled by default, at least with most distributions. 8.3 it's by default with most distributions. # show autovacuum; will tell you

[GENERAL] very slow updates in 8.3?

? Cheers, Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Compiling trigger function with MinGW

collect2: ld returned 1 exit status What should I do? -- Anton Burkun +380 66 757 70 27 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Compiling trigger function with MinGW

collect2: ld returned 1 exit status What should I do? -- Anton Burkun +380 66 757 70 27 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] recommended way of separating data from indexes

recommended way of doing this? Just using symbolic links? Help! Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] recommended way of separating data from indexes

20 p/d, so nothing) and for the moment we aren't doing any real logging apart from postgres internal (so not even activity logs or query logs, etc). So given that disk usage is pretty much 100% pgsql, and it's mainly read, does my architecture stand up? And thanks, I'll have a read

[GENERAL] move database from the default tablespace to a new one?

Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Order by parameter inside pgsql function ignored

ALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000; -- Best regards, Anton Marchenkov. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] citext data type does not work with JDBC PreparedStatement?

+ resultSet.getString(1)); } else { System.out.println("password not found"); } the password would be again returned. Any ideas why this can happen and how to fix this? thank's Anton

Re: [GENERAL] citext data type does not work with JDBC PreparedStatement?

On Sat, Aug 6, 2011 at 1:49 PM, Craig Ringer wrote: > On 6/08/2011 5:28 PM, Anton Moiseev wrote: > >> Hi, >> >> I wanted to have case-insensitive user names in my db and found that >> citext postgresql data type >> (http://www.postgresql.org/**doc

[GENERAL] consequent PQsendQueryPrepared() failed: another command is already in progress

I'm using libpq C Library. I prepared some query and trying to call it many times. But it success only at first time, and then fail with error: ... "another command is already in progress" Here is my testbed: int main (register int const argc, register char *const argv[]) { PGconn

Re: [GENERAL] consequent PQsendQueryPrepared() failed: another command is already in progress

2010/6/16 Tom Lane : >> But it success only at first time, and then fail with error: >> ... "another command is already in progress" >> [ PQsendQuery followed by just one PQgetResult ] > > IIRC, you need to keep calling PQgetResult until it returns NULL > before the connection is considered cleared

[GENERAL] Script to export all privileges to csv or similar

Hi, Does anyone know of a script/tool that allows one to export all users with all privileges? I realise I could construct a query to do it but google turned up nothing and if someone else has done the good work... Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq&

[GENERAL] R-tree, order by, limit

sive answers and links are appreciated. Thanks. Anton. -- 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] R-tree, order by, limit

2008/9/21 Volkan YAZICI <[EMAIL PROTECTED]>: > On Sun, 21 Sep 2008, "Anton Belyaev" <[EMAIL PROTECTED]> writes: >> SELECT * FROM towns where alt1 <= alt <= alt2 AND long1 <= long <= >> long2 ORDER BY population LIMIT 10; > > You're

Re: [GENERAL] R-tree, order by, limit

2008/9/21 Anton Belyaev <[EMAIL PROTECTED]>: > Hello, > > I am implementing a map application. There are towns with altitude, > longitude and population. > One of the tasks is to be able to query N biggest (by population) > towns within a rectangle. > > Something

Re: [GENERAL] R-tree, order by, limit

2008/9/21 Martijn van Oosterhout <[EMAIL PROTECTED]>: > On Sun, Sep 21, 2008 at 06:17:39PM +0400, Anton Belyaev wrote: >> Geometry types and functions use R-tree indexes anyways. >> >> I can rephrase the query using geometry language of Postgres: >> SELECT * F

Re: [GENERAL] R-tree, order by, limit

. It is too complex for my simple task and it gives no advantages for me: For spatial indexing it uses the same GiST-based R-tree. And PostGIS does not offer that "population" or "priority" queries I need. Anton. -- Sent via pgsql-general mailing list (pgsql-general@postgres

[GENERAL] problem with check constraints

Hi, When I create a check constraint in PgAdmin3 1.8.4 on a Postgresql 8.3.3: ((A and B) or (C and D)) I get with create script: (A and B or C and D) which is wrong. Please help. Cheers, Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Huge iowait during checkpoint finish

checkpoint_segments = 45 checkpoint_timeout = 60min checkpoint_completion_target = 0.9 I had mostly the same config with my 8.3 deployment. But hardware is different: Disk is software RAID-5 with 3 hard drives. Operating system is Ubuntu 9.10 Server x64. Thanks. Anton. -- Sent via pgsql-general

Re: [GENERAL] Huge iowait during checkpoint finish

Hello Greg, Thanks for you extensive reply. 2010/1/9 Greg Smith : > Anton Belyaev wrote: >> >> I think all the IOwait comes during sync time, which is 80 s, >> according to the log entry. >> > > I believe you are correctly diagnosing the issue.  The "sync ti

Re: [GENERAL] Huge iowait during checkpoint finish

2010/1/9 Scott Marlowe : > On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith wrote: >> Basically, you have a couple of standard issues here: >> >> 1) You're using RAID-5, which is not known for good write performance.  Are >> you sure the disk array performs well on writes?  And if you didn't >> benchmar

Re: [GENERAL] Memory Usage and OpenBSD

2010/1/28 Scott Marlowe : >> related to maximum per-process data space.  I don't know BSD very well >> so I can't say if datasize is the only such value for BSD, but it'd be >> worth checking.  (Hmm, on OS X which is at least partly BSDish, I see >> -m and -v in addition to -d, so I'm suspicious Op

Re: [GENERAL] Memory Usage and OpenBSD

2010/2/9 Scott Marlowe : > On Tue, Feb 9, 2010 at 3:18 AM, Anton Maksimenkov wrote: >>> Isn't the usual advice here is to log the ulimit setting from the pg >>> startup script so you can what it really is for the user at the moment >> I think that "su" i

Re: [GENERAL] Memory Usage and OpenBSD

2010/2/10 Martijn van Oosterhout : >> Can anybody briefly explain me how one postgres process allocate >> memory for it needs? > > There's no real maximum, as it depends on the exact usage. However, in > general postgres tries to keep below the values in work_mem and > maintainence_workmem. Most of

[GENERAL] ERROR: value out of range: underflow

Hi. I have a simple function. CREATE OR REPLACE FUNCTION myf_convert_phone18digits( in_phone VARCHAR ) RETURNS BIGINT -- IMMUTABLE AS $$ DECLARE t_extent_len BIGINT; t_phone_18 BIGINT; t_multiplier BIGINT; BEGIN IF in_phone IS NULL OR in_phone = '' THEN RAISE EXCEPTION 'in_phone[%] I

Re: [GENERAL] ERROR: value out of range: underflow

2010/2/26 Anton Maksimenkov : > When I try it in pgAdmin, first time it show me error: > -- > ERROR:  value out of range: underflow > CONTEXT:  PL/pgSQL function "myf_convert_phone18digits" line 12 at assignment > -- > SECOND (and consequences) time i

[GENERAL] autovacuum blues

Hi, I just can't understand why autovacuum is not working. I have a test db/table which I insert values into (by the thousands) and can't work out why my stats don't get updated. Could someone have a quick look at my attached .conf and tell me what I am doing? I am running it on FC5 8.1.4.fc5.1. C

Re: [GENERAL] autovacuum blues

On 09/11/06, Richard Huxton wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a

Re: [GENERAL] autovacuum blues

On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. C

Re: [GENERAL] autovacuum blues

On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply

Re: [GENERAL] autovacuum blues

The idea is that you _can_ do without a cronjob. You may need to do a first ANALYZE just to get things warmed up, and then let autovacuum do its job. Thanks guys, I think I have it now! Cheers Antoine ---(end of broadcast)--- TIP 4: Have you sear

[GENERAL] "stopping" a single database in a cluster

Hi, Is it possible to stop a single database, like it is with Oracle? Our oracle admin asked this for FS backups, if there are 3 dbs (in separate tablespaces) then there seems no reason why this couldn't be possible... is it possible? Cheers Antoine ps. If this isn't possible, what experiences do

[GENERAL] transfer just the data directories from linux to windows?

Hi, We have some backups from our prod server and I was wondering if there would be any problems with just copying the data directory to a windows install (same version - 8.1.4) ... any pointers? Cheers Antoine ---(end of broadcast)--- TIP 6: explai

Re: [GENERAL] 8.1.5 installation problem with initdb on WinXP Home

Someone posted a solution to this in the last couple of days on this list. Have a good look... Cheers Antoine On 30/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, I apologize for being yet another noob posting about this problem. I have spent 2 days searching the archives and the net

[GENERAL] n00b RAID + wal hot standby question

Hi, I am just starting at a company and we are inheriting a previously built solution. It looks pretty good but my previous experience with pg is seriously small-time compared with this... I am very new at the job, and don't know what hd config we have but it will be RAID-something I imagine (hey

Re: [GENERAL] n00b RAID + wal hot standby question

Thanks all for your very insightful and helpful answers. I will be able to really spend some time thinking about how the db will evolve (and so whether it is worth thinking about a change) in a week or so and will be able to think more on your answers then. Cheers Antoine

[GENERAL] temp tables and function performance

Hi, I am trying to move up in the world with my sql and need to do the following... I have a subscribers table and I need to export to csv (semi-colon separated) certain fields - that is fine, but I also need to export a multi-select field from another table as one string (0 to n values separated

Re: [GENERAL] Converting 7.x to 8.x

On 26/01/07, Jim Nasby <[EMAIL PROTECTED]> wrote: On Jan 25, 2007, at 12:47 PM, Benjamin Smith wrote: > On Tuesday 23 January 2007 13:55, Carlos wrote: >> What would be the faster way to convert a 7.4.x database into an 8.x >> database? A dump of the database takes over 20 hours so we want >> to

Re: [GENERAL] Questions about horizontal partitioning

However, if the primary key is entirely within those six columns, there will have to be an index on it in both tables to enforce the primary key constraint. In that case, an inner join could be performed with an index lookup or an index scan plus hash join, for a query that didn't use any oth

[GENERAL] drop table if exists mytable;

Hi, I need to do a "drop table if exists" type thing. I realise I can easily look in pg_tables, but for testing (if), don't I need to use a procedural language? In which case, I will need to install it if it doesn't exist - but I don't know how to test to see whether a language exists without usin

Re: [GENERAL] drop table if exists mytable;

> I need to do a "drop table if exists" type thing. I realise I can Install 8.2 or use this function, posted by David Fetter: Thanks for your answers... so this really was something that was missing (I think it a little rich to come out with a "are you using a version without this" when it has

[GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

Hi, I am trying to port an app to postgres and have come up against a most annoying problem. The app works with both mysql and sqlserver, who both seem to have a bit datatype instead of a proper boolean like pg. Alas, pg won't accept 1 and 0 for boolean... and npgsql won't convert my numeric(1) in

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

On 12/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Anton Melser wrote: >> Is there any way >> to force pg to accept 1 and 0 for boolean? > postgres=# insert into bool_test values(1::boolean); > INSERT 166

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

select oid from pg_type where typname = 'bool')) For the archives. Thanks to everyone. Anton ps. This is probably only for situations where it is absolutely necessary... but I am now passing my nunit tests! :-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

On 12/02/07, Anton Melser <[EMAIL PROTECTED]> wrote: > I think actually what he needs is what Peter suggested upthread, namely > to weaken the context-restriction on the int-to-bool cast. Indeed... Peter's suggestion seems to have solved all my problems. So even though it pr

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

On 13/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > ERROR: operator is not unique: boolean = integer > I get this whether castcontext is 'a' or 'i'. If you make both cast directions the same priorit

[GENERAL] greedy or not? regexps...

url_id, '=([0-9]{1,10})') from banner ban1 where ban1.url_id ~* '.*linkadministration.*[0-9]*'; Both give me the same result!!! The difference being that in case two the numbers I am catching are at the end of the strings and in case 1 in the middle. Is this normal? Which is correct? Cheers Ant

[GENERAL] select all matches for a regular expression ?

Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast

Re: [GENERAL] select all matches for a regular expression ?

On 23/02/07, Peter Childs <[EMAIL PROTECTED]> wrote: On 23/02/07, Anton Melser <[EMAIL PROTECTED]> wrote: > Hi, > I need to be able to get all the matches for a particular regexp from > a text field that I need to use in another query in a function. Is > this possible wi

Re: [GENERAL] greedy or not? regexps...

Intellectually challenged Anton strikes again! I wanted +, not *. Sometimes I think I'm not cut out for IT! :-( Thanks heaps, Anton On 23/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > I am trying to understand the fu

Re: [GENERAL] select all matches for a regular expression ?

On 23/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > I need to be able to get all the matches for a particular regexp from > a text field that I need to use in another query in a function. Is > this possible with plpgsql

[GENERAL] stored procedure optimisation...

Hi, I need to analyse some html to get some links out, and with only 25 lines in exports_tmp_links (and text_to_parse no more than around 10KB) this function has taken 10 minutes and counting. Something horribly wrong is going on here! Can someone give me any pointers? Cheers Anton delete from

Re: [GENERAL] stored procedure optimisation...

RAISE NOTICE 'I am here, and myvar = % and thatvar = %', myvar, thatvar; Thanks... it is indeed a gem that little instruction!!! Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend

  1   2   >