Temp rows - is it possible? - Page 3

Boris Popov

Hello pgsql-general,

I'm trying to implement a table with rows that are automatically
deleted when the session that inserted them disconnects, sort of like
our own alternative to pg_stat_activit y. Is it possible and what
approach should I be trying to achieve such a thing?

Thanks!

--
-Boris

---------------------------(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

Nov 12 '05

Subscribe Reply

3232

Robert Creager

When grilled further on (Tue, 11 Nov 2003 11:39:02 +0530),
Shridhar Daithankar <sh************ *****@myrealbox .com> confessed:

them). Would there be an alternate method to easily create a temp table
that is identical to another?

You can use LIKE clause in create table.

See http://developer.postgresql.org/docs...eatetable.html

Thanks Shridhar,

I didn't know there was a LIKE clause, and it's even right above INHERITS in
the docs... I also didn't realize that the parent table would see all the
child tables data when using INHERITS. I should be using LIKE...

Cheers,
Rob

--
09:58:11 up 102 days, 3:16, 5 users, load average: 1.05, 1.11, 1.41

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj+ xFikACgkQLQ/DKuwDYzm3SQCeKM OFS/pN4bRAsGt9Iuan5 +WL
wcUAoIt5/KTEH5KJpi8oV267 jWp4UDZf
=8QCB
-----END PGP SIGNATURE-----

Nov 12 '05 #21

Bruce Momjian

Tom Lane wrote:

Bruce Momjian <pg***@candle.p ha.pa.us> writes:
Tom Lane wrote:
We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

Added to TODO:

* Ignore temporary tables from other session when processing
inheritance

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.ph a.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(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

Nov 12 '05 #22

Similar topics

12530

Temp Table on Linked Server

by: Ootyguy | last post by:

Trying to do this all day and googling for answers but found none, hope someone can help. Thanks in advance. select * into OPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTable Reason: I am joining local tables with linked server tables using the format "LinkedServer.database.owner.object" to execute a query, it takes forever to execute since the tables joined on the remote servers

Microsoft SQL Server

3562

asp.net Oracle TEMP table woes

by: Phil Short via DotNetMonster.com | last post by:

I'm having trouble using a temp table in Oracle. Here's my code: Dim myConn As OleDbConnection Dim strconn As String = Session.Item("optSrcDBConnect") Dim dcSQL As OleDbCommand Dim strSQL As String Dim drSQL As OleDbDataReader WriteLog("Open connection")

ASP.NET

1242

unexpected update behavior with temp tables

by: Timothy Perrigo | last post by:

This bug? feature? caused a bit of havoc for us yesterday...A reproducible example follows. Essentially, if you have a table with a primary key called "id", and you create a temp table (via a "select into") containing a subset of the data from the table but where the primary key field is renamed (in the example below, it is called "not_id"), the where clause of the following update statement (which I would expect to generate an error...

PostgreSQL Database

2998

Temp Table Faster?

by: wackyphill | last post by:

If you were doing paging of results on a web page and were interested in grabbing say records 10-20 of a result set. But also wanted to know the total # of records in the result set (so you could know the total # of pages in the set). Would it be better to query the DB table 2X. Once for Count(*). And again for the records for the current page? Or better to create a temp table, select the records into it, and then get count(*) and the...

Microsoft SQL Server

9621

Deadlock on Update using temp table

by: fmatamoros | last post by:

I sometimes get the following error from an update statement in a stored procedure: Transaction (Process ID 62) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The isolation level is READ UNCOMMITTED and there are no explicit transactions in the stored procedure. The update statement is as follows:

Microsoft SQL Server

2013

Temp database wouldn't open from jump drive

by: robert d via AccessMonster.com | last post by:

When my app starts up, it creates a temporary database. This temp database is created from a 'model' database that is in the same folder as the application. Because there is a model, the creation is essentially to just copy the 'model' database and give it the name of the application with the extension ".tmp". Then code opens the temp database and links to the tables. This has always worked flawlessly until today. Today, I needed to...

Microsoft Access / VBA

5552

The age old argument of Temp table vs Table variable

by: Burbletrack | last post by:

Hi All, Hope someone can help me... Im trying to highlight the advantages of using table variables as apposed to temp tables within single scope. My manager seems to believe that table variables are not advantageous because they reside in memory. He also seems to believe that temp tables do not use memory...

Microsoft SQL Server

3509

Temp tablespace - system or user?

by: Arun Srinivasan | last post by:

I have a sql that goes like select ............... from table (select ....... from table t where <>) as x Now this temp table x is supposed to keep 10 K rows at a point of time. Where would they be stored? I know the answer is temp tablespace, but is it in system temp or user temp? All I am trying to do is to create a temp (sys or user) with good amount of pages so that these kinds of queries would speed up. I dont

DB2 Database

9656

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9498

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10364

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10110

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8993

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7517

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5536

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3670

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2894

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General