Rolled-back temporary table remains in session lookup and shadows schema-qualified tables

### Description

A temporary table created inside a transaction remains in Doltgres’ internal session lookup after the transaction is rolled back.

The stale entry can shadow a subsequently created schema-qualified table with the same unqualified name. DDL and inserts are then resolved against the rolled-back temporary table definition.

### Reproduction

```sql
CREATE TABLE int8_tbl(q1 BIGINT, q2 BIGINT);
INSERT INTO int8_tbl VALUES (123, 456);

BEGIN;
CREATE TEMP TABLE t1 AS SELECT * FROM int8_tbl;
ROLLBACK;

SELECT to_regclass('t1');

CREATE SCHEMA alter1;
CREATE TABLE alter1.t1(
f1 SERIAL PRIMARY KEY,
f2 INT CHECK (f2 > 0)
);

INSERT INTO alter1.t1(f2) VALUES (11);
INSERT INTO alter1.t1(f2) VALUES (12);
```

### Actual behavior

`to_regclass('t1')` returns `NULL`, indicating that the temporary table is no longer visible through PostgreSQL catalog resolution.

However, creation of `alter1.t1` fails with an error involving the rolled-back temporary table’s columns:

```text
column "f1" referenced in primary key constraint does not exist
```

The subsequent inserts fail with:

```text
Unknown column 'f2' in 't1'
```

### Expected behavior

PostgreSQL rolls back `CREATE TEMP TABLE` with the surrounding transaction. After `ROLLBACK`:

- No temporary `t1` should remain in session lookup.
- `CREATE TABLE alter1.t1(...)` should succeed.
- Schema-qualified references to `alter1.t1` should resolve to that table.
- Both inserts should succeed.

### Likely cause

Dolt tracks temporary tables in the session-level `DoltSession.tempTables` map. That state is not included in transaction rollback or savepoint restoration.

In addition, table lookup checks session temporary tables before persisted tables. This allows a stale unqualified temporary-table entry to take precedence during resolution of a schema-qualified Doltgres relation.

Relevant shared-layer areas include:

- `libraries/doltcore/sqle/dsess/session.go`
- `libraries/doltcore/sqle/database.go`

The regression can be reproduced through:

- `testing/go/regression/tests/alter_table.sql`

### Proposed fix

Implement PostgreSQL-compatible temporary-table lifecycle handling in Doltgres:

- Journal or snapshot temporary-table creation and deletion with transaction state.
- Restore that state on transaction rollback and savepoint rollback.
- Ensure explicitly schema-qualified PostgreSQL relations do not resolve to an unrelated temporary-table entry.
- Preserve Dolt/MySQL behavior, where `CREATE TEMPORARY TABLE` is not undone by `ROLLBACK`.

If the shared Dolt session API cannot support this without changing MySQL behavior, add an opt-in policy or transaction hook that Doltgres can use.

### Acceptance tests

Cover:

1. Temporary table created inside a transaction and then rolled back.
2. Temporary table created and committed.
3. Temporary table dropped inside a transaction and then rolled back.
4. Temporary table creation and deletion across savepoints.
5. Reusing the same unqualified name in a non-temporary schema after rollback.
6. Schema-qualified lookup when a temporary table has the same name.
7. Catalog visibility through `to_regclass`.
8. Preservation of existing Dolt/MySQL temporary-table rollback behavior.

### Context

Exposed while testing Doltgres PR #3274. The CTAS work does not appear to introduce the underlying lifecycle or lookup behavior; it makes the affected execution path reachable.

Read libraries/doltcore/sqle/dsess/session.go and libraries/doltcore/sqle/database.go to trace temporary-table state and relation lookup. Run testing/go/regression/tests/alter_table.sql and reproduce the rollback case first. Done means PostgreSQL temporary-table state restores across transactions and savepoints, schema-qualified lookup is correct, catalog visibility matches PostgreSQL, and Dolt/MySQL behavior remains unchanged.

Written by the indexing model from the issue text.