Re: [GENERAL] temporary indexes?

Jonathan Vanasco wrote: > I couldn't find any mention of this on the archives... > > Have the project maintainers ever considered extending CREATE INDEX to > support "temporary" indexes like CREATE TEMPORARY TABLE? Not sure if you mean something like this:

Re: [GENERAL] temporary indexes?

Jim Nasby writes: > On 10/21/15 3:28 PM, Jonathan Vanasco wrote: >> Transactions and table-locking issues are probably why temporary indexes >> don't exist. > I think it's more that no one has proposed it until now. It probably > wouldn't be terribly hard to add

Re: [GENERAL] carray_to_bytea?

Yeah, I thought of that (actually bytea *), but then you have to assume that bytea and text will always be typedef'ed to the same thing. (Is casting between different non-void pointer types, even if they are compatible, a warning/error in C? I can't remember.) On Thu, Oct 22, 2015 at 5:16 PM,

Re: [GENERAL] temporary indexes?

Jim Nasby writes: > On 10/22/15 12:36 PM, Tom Lane wrote: >> Uh, why would you do that? You'd be throwing away one of the principal >> performance advantages of temp tables. > Actually, it depends on what behavior you'd expect from a temporary > index. If it was only

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

On 10/22/2015 10:03 AM, anj patnaik wrote: Adrian, I am doing same steps as you. And my byte count matches yours. It is a RHEL 6.5 Linux machine. If the byte count matches how can it be incomplete? I am baffled. Thanks for any clues! bash-4.1$ ls -all total 132452 -rwxrwxrwx 1 apatnaik

Re: [GENERAL] ID column naming convention

On 10/22/15 11:37 AM, Karsten Hilbert wrote: On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote: BTW, if there's any interest, I have code that sets up a non-inheritance table specifically for doing foreign keys to the inheritance parent. It doesn't support updates right now, but should

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On 10/21/15 9:32 PM, Dane Foster wrote: "If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the query, or to nulls if the query returned no rows." ​Foot removed from mouth. Note however that there's some unexpected things when

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

On 10/22/2015 10:03 AM, anj patnaik wrote: Adrian, I am doing same steps as you. And my byte count matches yours. It is a RHEL 6.5 Linux machine. If the byte count matches how can it be incomplete? I am baffled. Thanks for any clues! bash-4.1$ ls -all total 132452 -rwxrwxrwx 1 apatnaik

Re: [GENERAL] carray_to_bytea?

On 10/22/15 12:13 PM, Ken Been wrote: My input is a byte array with a length. I can't assume zero-termination for varchar fields, so cstring_to_text_with_len is exactly what I need for those. For varbinary (i.e., bytea), you're right, it's just a couple of lines of code, but what if the

Re: [GENERAL] temporary indexes?

On 10/22/15 12:36 PM, Tom Lane wrote: Jim Nasby writes: On 10/21/15 3:28 PM, Jonathan Vanasco wrote: Transactions and table-locking issues are probably why temporary indexes don't exist. I think it's more that no one has proposed it until now. It probably

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On 10/22/15 11:25 AM, bricklen wrote: I would have liked to have had the opportunity to answer those questions myself but alas, in the heat of the moment some of the data useful for forensics was lost. You could always roll WAL forward from the previous base backup and see what happens.

Re: [GENERAL] temporary indexes?

On 21-10-15 20:43, Jonathan Vanasco wrote: I couldn't find any mention of this on the archives... Have the project maintainers ever considered extending CREATE INDEX to support "temporary" indexes like CREATE TEMPORARY TABLE? When creating temporary tables for analytics/reporting, I've

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 2:08 PM, Tom Lane wrote: > FWIW, I don't find much attraction in the idea of building an index for > use by a single query. There basically isn't any scenario where that's > going to beat running a plan that doesn't require the index. The value of > an index is generally to

Re: [GENERAL] temporary indexes?

On 10/22/15 3:15 PM, Jonathan Vanasco wrote: On Oct 22, 2015, at 8:17 AM, vincent elschot wrote: Do you mean creating a temporary index on a non-temporary table to speed up the queries that fills the temporary table? One of the use-cases is speeding up inserts on create, but another is for

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 5:04 PM, Jim Nasby wrote: > > What % of execution time is spent creating those indexes? Or is that factored > into the 1000%? Also, could your analysis queries be run in a REPEATABLE READ > transaction (meaning that once the transaction starts it doesn't get any new >

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby wrote: > On 10/21/15 9:32 PM, Dane Foster wrote: > >> "If STRICT is not specified in the INTO clause, then target will be >> set to the first row returned by the query, or to nulls if the query >> returned no rows."

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

Adrian, I am doing same steps as you. And my byte count matches yours. It is a RHEL 6.5 Linux machine. If the byte count matches how can it be incomplete? I am baffled. Thanks for any clues! bash-4.1$ ls -all total 132452 -rwxrwxrwx 1 apatnaik oprofile 37659362 Oct 20 20:41

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster wrote: > On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro > wrote: >> >> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster wrote: >> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro wrote: > On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster wrote: > > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby > wrote: > >> On 10/21/15 9:32 PM, Dane Foster wrote: > >>>

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster wrote: > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby wrote: >> On 10/21/15 9:32 PM, Dane Foster wrote: >>> >>> "If STRICT is not specified in the INTO clause, then target will be >>> set to the

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro wrote: > On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster wrote: > > On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro > > wrote: > >> > >> On Fri, Oct 23, 2015 at 10:27

Re: [GENERAL] Multiple word synonyms (maybe?)

On Wed, 21 Oct 2015 13:40:38 + (UTC) Kevin Grittner wrote: > > Damn, I completely overlooked that one, and it indeed does seem > > to come very close to what I need in this use case. > > I have to admit that the name of that dictionary type threw me off > a bit at first.

Re: [GENERAL] Migrate whole cluster to utf8

On 10/21/2015 06:34 PM, NTPT wrote: Hi all I have a db cluster (around 50GB of data ) in LATIN2 encoding. Now I need to dump whole cluster because of upgrade to newer version of pstgresql. But I need to have new cluster created with utf8 encoding And databases in that clusters tooo (with

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

A follow-up question if I may, > bricklen writes: >> > Yes, it is definitely a table. There was originally an index on that >> table >> > which threw the original error (about sibling mismatch). I dropped the >> > index and attempted to recreate it, which failed. Further

[GENERAL] carray_to_bytea?

I'd like to propose a carray_to_bytea function, similar to cstring_to_text_with_len, declared in src/include/utils.h and implemented in src/backend/utils/adt/varlena.c. The implementation would be the same as cstring_to_text_with_len, but with a different return type. I have put the

Re: [GENERAL] ID column naming convention

On Mon, Oct 19, 2015 at 04:25:39AM -0500, Jim Nasby wrote: > BTW, if there's any interest, I have code that sets up a non-inheritance > table specifically for doing foreign keys to the inheritance parent. It > doesn't support updates right now, but should be fully safe from a FK > standpoint.

Re: [GENERAL] carray_to_bytea?

Ken Been writes: > I'd like to propose a carray_to_bytea function, similar to > cstring_to_text_with_len, declared in src/include/utils.h and implemented > in src/backend/utils/adt/varlena.c. The implementation would be the same > as cstring_to_text_with_len, but with a

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

bricklen writes: > There are several hot standby servers attached to the master, some > streaming, and one in a different data centre that is using WAL shipping > only. > The streaming slave IIRC got the corruption from the master (I can't check > now, it was rebuilt). > What

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On Thu, Oct 22, 2015 at 9:15 AM, Tom Lane wrote: > bricklen writes: > > What would have happened to the WAL-shipping-only standby if the WALs > were > > all applied? Would it have it balked at applying a WAL containing bad > data > > from the master, or

Re: [GENERAL] carray_to_bytea?

On Thu, Oct 22, 2015 at 4:12 PM, Tom Lane wrote: > Ken Been writes: > > I'd like to propose a carray_to_bytea function, similar to > > cstring_to_text_with_len, declared in src/include/utils.h and implemented > > in src/backend/utils/adt/varlena.c. The

Re: [GENERAL] temporary indexes?

On 10/21/15 3:28 PM, Jonathan Vanasco wrote: On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote: I misunderstood then. The only thing I can think of is to wrap in a transaction, though that presents other issues with open transactions and/or errors in the transaction. I just explicitly